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?
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
Unknown said: 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.
Nice.