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?
Agreed…
Love the detail. I have never used cross apply before. Mwah ha ha….
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Chris Jensen Sent: Tuesday, April 19, 2016 5:52 PM To: Dot Krebs Subject: Re: [Tessitura Technical Forum] SQL assistance
Nick Reilingh: 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.
Nick Reilingh:
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.
From: Nick Reilingh <bounce-nicholasreilingh4883@tessituranetwork.com> Sent: 4/19/2016 5:28:03 PM
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.
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
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!