- Home
- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges

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
Comment

**8** People are following this question.

Copyright 2022 Redgate Software.
Privacy Policy