question

muk avatar image
muk asked

query help please!

I have this query to get all information about donors at our school. I am trying to write a report. It gets the First Donation, Last Donation, Highest, Smallest, and all of their info (street adress, etc.) I need to add two more things: the sum of all gifts of type 'GF' and the sum of all gifts of type 'PP' can anyone help me with this? I would be so appreciative!!!! THANKS!!!! select 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], 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 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] 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 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 = '0000186';
sql-server-2008-r2query
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

·
Usman Butt avatar image
Usman Butt answered
How about this select 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], 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.GF_Gift_Amount, c.PP_Gift_Amount 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) GF_Gift_Amount, SUM(CASE WHEN CONTRIB_TYPE = 'PP' THEN amt ELSE 0 END) PP_Gift_Amount 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 = '0000186';
4 comments
10 |1200

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

muk avatar image muk commented ·
Hi Usman, Thank you for the response. I tried this and got the error "the multi-part identifier c.CONTRIB_TYPE could not be bound."
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
I have edited my answer. Please check it again.
0 Likes 0 ·
muk avatar image muk commented ·
Wonderful, THANK YOU!!!!!!!!!!!
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
Happy to help ;)
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.