Don't you hate it when SQL gives you what you ask for and not what you need? I could use a little help in that area.
What I want is the last contribution date, amount and ref_no (to use elsewhere) in a specific set of campaigns.
I found a helpful script in the Output set builder cookbook, but it is designed around using max ref_no. I need to adjust that to last cont_dt because the converted data is a little out of order. Joy.
What gets me closest to what I want is this:
SELECT a.customer_no, a.ref_no, MAX(a.cont_dt) AS cont_dt
FROM dbo.VS_CONTRIBUTION AS a (NOLOCK)
JOIN VS_FUND b (NOLOCK) on a.fund_no = b.fund_no
WHERE (a.campaign_no in(1, 34, 38, 39, 41, 51, 68, 77, 84, 92, 100, 107, 115, 121, 154, 232, 239, 245, 251, 257, 263, 269, 275, 281, 288, 296, 303, 311, 319, 327, 335, 343, 351, 359, 368, 378, 387, 395, 446)) and (a.recd_amt > 0) AND (b.desig_code IN (1))
GROUP by a.customer_no, a.ref_no
But I believe that is giving me the last transaction by campaign. What do I need to do to get the very last contribution to any of the included campaigns with the ref_no by constituent?
Dot
This is a quick way of doing this, it might not be the most efficient though.
SELECT *
FROM (
SELECT *, pos = ROW_NUMBER() OVER(PARTITION BY customer_no ORDER BY customer_no, cont_dt DESC)
FROM T_CONTRIBUTION
) a
WHERE a.pos = 1
Fernando Margueirat Senior Business Analyst The National Ballet of Canada 470 Queens Quay West Toronto, Ontario M5V 3K4 P: 416 345 9686 x453 F: 416 345 8323
http://national.ballet.ca
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Dot Krebs Sent: Wednesday, April 20, 2016 10:12 AM To: Fernando Margueirat <FMargueirat@national.ballet.ca> Subject: RE: [Tessitura Technical Forum] SQL assistance
Nick,
Your assessment is close.
We want one line for each customer and the last campaign to which they donated.
I’m playing with the cross apply. We’ll see.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nick Reilingh Sent: Tuesday, April 19, 2016 5:41 PM To: Dot Krebs Subject: Re: [Tessitura Technical Forum] SQL assistance
You want one row per campaign that contains data from only the most recent contribution in that campaign, right?
My approach to this would be to have my primary SELECT FROM be for VS_CAMPAIGN, and then join to that using a CROSS APPLY where the subquery does a TOP 1 ORDER BY cont_dt DESC.
If you've never used cross apply/outer apply before, they're basically just like inner/outer joins, except instead of having a table or subquery for the entire join (which you match using an ON clause), you get to join to the entire subquery for every single row in the table you're joining to.
SO it would be something like this:
SELECT a.campaign_no, a.description, topcont.*
FROM VS_CAMPAIGN a
CROSS APPLY ( SELECT TOP 1 *
FROM VS_CONTRIBUTION b
WHERE b.campaign_no = a.campaign_no
ORDER BY b.cont_dt DESC ) AS topcont
From: Dot Krebs <bounce-dotkrebs9366@tessituranetwork.com> Sent: 4/19/2016 5:09:05 PM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Unknown said: This is a quick way of doing this, it might not be the most efficient though.
Also nice; an elegant de-dupe, e.g.
SELECT * FROM ( SELECT co.customer_no ,co.ref_no ,co.cont_amt ,co.cont_dt ,pos = ROW_NUMBER() OVER(PARTITION BY customer_no ORDER BY customer_no, cont_dt DESC) FROM T_CONTRIBUTION co join T_CAMPAIGN ca on co.campaign_no = ca.campaign_no where ca.fyear = 2016 -- for example ) a WHERE a.pos = 1 order by cont_dt desc ,ref_no desc
I've been meaning to learn more about window functions--thanks for a simple example!
Unknown said: I've been meaning to learn more about window functions--thanks for a simple example!
I concur; an enjoyable thread!