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_amtfrom vs_contributiongroup 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
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.
Thanks Brian!
Your suggestion got me thinking a little more, and I adapted your thinking into this:
select a.customer_no, a.cont_amt, a.cont_dt, a.campaign_no –-etc.
from vs_contribution a
join (select aa.customer_no, max(aa.ref_no) as ref_no
from vs_contribution aa
join (select customer_no, max(cont_amt) as max_cont_amt
from vs_contribution
group by customer_no) bb
on aa.customer_no=bb.customer_no and aa.cont_amt=bb.max_cont_amt
group by aa.customer_no) b
on b.customer_no=a.customer_no and b.ref_no=a.ref_no
It does choose between different contributions with the same amount by selecting the largest ref_no (which should be the most recent gift of that amount), but I think it will do what I need. See any potential problems?
Database Administrator
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?
Nice! Thanks Brian and David!
Thanks, Kirk, David, and Brian; this really helped with something I’m working on, too!
Lucie at FGO