• Replies 5 replies
  • Subscribers 337 subscribers
  • Views 194 views
  • Users 0 members are here
Related

sql help on largest gift per customer

Kirk Mortensen
$organization

I'm trying to put together a custom view for some output elements that would give me various fields from the largest contribution for each customer.

So I've gotten this far in my select statement:

select customer_no, max(cont_amt) as cont_amt
from vs_contribution
group by customer_no

But how do I get the ref_no of this largest contribution in there so I can join to the full contribution record and get other info, like gift date, campaign, source, etc.?

Any help would be much appreciated!

Thanks,

Kirk Mortensen

TheatreWorks

Parents
  • $organization

    Here is another way, which is inefficient, but works. It involves using the row_number() function to number all gifts by customer by cont_amt descending, then deleting anything but the #1 entries. The advantage is that you can pull any information you need into the #gifts table.

     

    create table #gifts(

    counter int,

    customer_no int,

    ref_no int,

    cont_amt money

    )

     

    insert into #gifts

    select row_number() over (partition by customer_no order by cont_amt desc), customer_no, ref_no, cont_amt

    from t_contribution

    delete from #gifts where counter <> 1

    select * from #gifts

     

    I realize this is not View friendly.

     



    [edited by: David Woodall at 3:20 PM (GMT -6) on 20 Sep 2010]
Reply
  • $organization

    Here is another way, which is inefficient, but works. It involves using the row_number() function to number all gifts by customer by cont_amt descending, then deleting anything but the #1 entries. The advantage is that you can pull any information you need into the #gifts table.

     

    create table #gifts(

    counter int,

    customer_no int,

    ref_no int,

    cont_amt money

    )

     

    insert into #gifts

    select row_number() over (partition by customer_no order by cont_amt desc), customer_no, ref_no, cont_amt

    from t_contribution

    delete from #gifts where counter <> 1

    select * from #gifts

     

    I realize this is not View friendly.

     



    [edited by: David Woodall at 3:20 PM (GMT -6) on 20 Sep 2010]
Children
No Data