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?
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.
Dot
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: We want one line for each customer and the last campaign to which they donated.
The "each customer" changes things. As above, this doesn't de-dupe for multiple contribs on the same date, but could be a start toward whatever you want to do with the data next.
select c.ref_no ,c.customer_no ,c.cont_amt ,c.cont_dt from T_CONTRIBUTION c join ( select co.customer_no ,max(co.cont_dt) as max_cont_dt from T_CONTRIBUTION co join T_CAMPAIGN ca on ca.campaign_no = co.campaign_no where ca.fyear = 2016 -- for example group by co.customer_no ) md on c.customer_no = md.customer_no and c.cont_dt = md.max_cont_dt order by c.cont_dt desc ,c.ref_no desc