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?
Unknown said: 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.
Something like this might get you started:
select c.ref_no ,c.customer_no ,c.cont_amt ,c.cont_dt from T_CONTRIBUTION c join ( select co.campaign_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.campaign_no ) md on c.campaign_no = md.campaign_no and c.cont_dt = md.max_cont_dt order by c.cont_dt desc ,c.ref_no desc