question

muk avatar image
muk asked

query help please

Hello everyone, I have this query that reports data on a specific donor (I work at a school) . It includes their largest donation, last donation, first donation, and sums of donations. I need to do two things: I need to change it to report this information for all donors in the table not just the ID I have specified and I need to add a collumn to compute the sum of all gifts of type 'NP'. The problem is that for all other computed collumns I only needed information on gifts of type 'PP' or 'GF' so I dont know how to add this one collumn. Any help is greatly appreciated. Thank you. And thanks to those who have already helped me come up with parts of this query. select pv.ID, pv.Prefix, pv.[First Name], pv.[Middle Name], pv.[Last Name], pv.[Company Name], pv.[Address Line 1], pv.[Address Line 2], pv.[Address Line 3], pv.City, pv.[State], pv.Zip, pv.[Address Type], pv.[Preferred Email], pv.Cell, pv.Home, c.[Life Largest Amount], c.[Largest Amount Date], c.[Life Smallest Amount], c.[Smallest Amount Date], c.[Last Amount], c.[Last Amount Date], c.[First Amount], c.[First Amount Date],c.[Sum of Gifts], c.[Sum Paid to Date] from PREFERRED_MAIL_VIEW pv cross apply ( select max(case when L = 1 then amt else null end) [Life Largest Amount], max(case when L = 1 then CONTRIB_DATE else null end) [Largest Amount Date], max(case when S = 1 then amt else null end) [Life Smallest Amount], max(case when S = 1 then CONTRIB_DATE else null end) [Smallest Amount Date], max(case when D=1 then amt else null end) [Last Amount], max(case when D=1 then CONTRIB_DATE else null end) [Last Amount Date], max(case when A=1 then amt else null end) [First Amount], max(case when A=1 then CONTRIB_DATE else null end) [First Amount Date], SUM(CASE WHEN CONTRIB_TYPE = 'GF' THEN amt ELSE 0 END) [Sum of Gifts], SUM(CASE WHEN CONTRIB_TYPE = 'PP' THEN amt ELSE 0 END) [Sum Paid to Date] from ( select cdd.CONDES_HARD_CREDIT_AMT amt, c.CONTRIB_DATE, row_number() over (order by CONDES_HARD_CREDIT_AMT desc) L, row_number() over (order by CONDES_HARD_CREDIT_AMT asc) S, row_number() over (order by CONTRIB_DATE desc) D, row_number() over (order by CONTRIB_DATE asc) A, CONTRIB_TYPE from CONTRIB_DONOR cd inner join CONTRIB_DONOR_DESIG cdd on cd.CONTRIB_DONOR_ID = cdd.CONDES_CONTRIB_DONOR inner join CONTRIBUTION c on cdd.CONDES_CONTRIBUTION = c.CONTRIBUTION_ID where cd.COND_DONOR = pv.ID and (c.CONTRIB_TYPE = 'GF' OR c.CONTRIB_TYPE = 'PP') ) t ) c where pv.ID = '0000959';
t-sqlsql-server-2008-r2sql-serverquery
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered
Since you need a report for all users instead of the specified one, just remove the **where pv.ID = '0000959'** part. Adding a sum of all donations of type NP will require a small change in the code. Because you need to consider this type as well, it needs toi be added to the list of contrib\_type values, currently listing only GF and PP. On the other hand, because other calculations should include only those types, you need to explicitly filter the other type in your case statements. For example, I remember that to calculate the largest amount you originally wanted to only consider GF types. If this is the case then you need to adjust the case statement accordingly. Currently, this line reads: max(case when L = 1 then amt else null end) [Life Largest Amount], If you need to consider only the amounts for contrib\_type = 'GF' then this line should instead read this: max(case when L = 1 and CONTRIB_TYPE = 'GF' then amt else null end) [Life Largest Amount], etc. This adjustment aside, here is the query which reports for all users and includes the additionaly column you need: select pv.ID, pv.Prefix, pv.[First Name], pv.[Middle Name], pv.[Last Name], pv.[Company Name], pv.[Address Line 1], pv.[Address Line 2], pv.[Address Line 3], pv.City, pv.[State], pv.Zip, pv.[Address Type], pv.[Preferred Email], pv.Cell, pv.Home, c.[Life Largest Amount], c.[Largest Amount Date], c.[Life Smallest Amount], c.[Smallest Amount Date], c.[Last Amount], c.[Last Amount Date], c.[First Amount], c.[First Amount Date], c.[Sum of Gifts], c.[Sum Paid to Date], c.[Sum NP Gifts] from PREFERRED_MAIL_VIEW pv cross apply ( select max(case when L = 1 then amt else null end) [Life Largest Amount], max(case when L = 1 then CONTRIB_DATE else null end) [Largest Amount Date], max(case when S = 1 then amt else null end) [Life Smallest Amount], max(case when S = 1 then CONTRIB_DATE else null end) [Smallest Amount Date], max(case when D=1 then amt else null end) [Last Amount], max(case when D=1 then CONTRIB_DATE else null end) [Last Amount Date], max(case when A=1 then amt else null end) [First Amount], max(case when A=1 then CONTRIB_DATE else null end) [First Amount Date], SUM(CASE WHEN CONTRIB_TYPE = 'GF' THEN amt ELSE 0 END) [Sum of Gifts], SUM(CASE WHEN CONTRIB_TYPE = 'PP' THEN amt ELSE 0 END) [Sum Paid to Date], sum(case when CONTRIB_TYPE = 'NP' then amt else null end) [Sum NP Gifts] from ( select cdd.CONDES_HARD_CREDIT_AMT amt, c.CONTRIB_DATE, row_number() over (order by CONDES_HARD_CREDIT_AMT desc) L, row_number() over (order by CONDES_HARD_CREDIT_AMT asc) S, row_number() over (order by CONTRIB_DATE desc) D, row_number() over (order by CONTRIB_DATE asc) A, CONTRIB_TYPE from CONTRIB_DONOR cd inner join CONTRIB_DONOR_DESIG cdd on cd.CONTRIB_DONOR_ID = cdd.CONDES_CONTRIB_DONOR inner join CONTRIBUTION c on cdd.CONDES_CONTRIBUTION = c.CONTRIBUTION_ID where cd.COND_DONOR = pv.ID and (c.CONTRIB_TYPE in ('GF', 'NP', 'PP')) ) t ) c; Oleg
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

wow thank you so much again Oleg. You have come through for me twice and I really appreciate it!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.