• Replies 5 replies
  • Subscribers 337 subscribers
  • Views 197 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

    I like the below, David, especially because I rarely think of using temp tables.  But since I rarely think of using temp tables, I can’t see an easy way of turning this into a view for an output element… any ideas?

     

    Kirk Mortensen

    Database Administrator

    TheatreWorks

     

    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

Reply
  • $organization

    I like the below, David, especially because I rarely think of using temp tables.  But since I rarely think of using temp tables, I can’t see an easy way of turning this into a view for an output element… any ideas?

     

    Kirk Mortensen

    Database Administrator

    TheatreWorks

     

    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

Children
No Data