question

Hello everyone, I have a query that gets data from multiple tables to do some aggregations. The data in these tables is stored as one line per donation. I created this query to report, Largest gift, largest gift date, smallest gift, smallest gift date, first gift, first gift date, last gift, and last gift date. I did not realize some special scenarios to code for so I am asking your assistance. Overall it works but here are the scenarios I need to code for: 1. First Gift Amount – If there are multiple gifts on the same day, we should add them together. 2. Last Gift Amount – If there are multiple gifts on the same day, we should add them together 3. Largest Gift Date – If there are multiple gifts for the same amount given on different dates, we should use the most recent gift. If two gifts happened on the same day as the largest gift. So someone gave $500 in the morning of today and that was their largest gift ever but they give $50 later in the SAME day, it should be treated as one largest gift of $550. Can anyone help me recode this query for these scenarios??? Here is the query: SELECT t1.ID , t1.Prefix , t1.[First Name] , t1.[Middle Name] , t1.[Last Name] , t3.SUFFIX , t3.PERSON_STATUS , t3.GENDER , t3.DECEASED_DATE , t3.MARITAL_STATUS , t1.[Company Name] , t1.[Address Line 1] , t1.[Address Line 2] , t1.[Address Line 3] , t1.City , t1.State , t1.Zip , t1.Country , t1.[Address Type] , t1.Email , t1.Cell , t1.Home , t1.[Business Phone] , t1.[Business Phone EXT] , t1.[Reunion Class 1] , t1.[Reunion Class 2] , t1.[Reunion Class 3] , t1.[Mail Rules 1] , t1.[Mail Rules 2] , t1.[Mail Rules 3] , t3.SPOUSE AS [Spouse ID] , t3.BIRTH_DATE , t1.[Life Largest Amount] , t1.[Largest Amount Date] , t1.[Life Smallest Amount] , t1.[Smallest Amount Date] , t1.[Last Amount] , t1.[Last Amount Date] , t1.[First Amount] , t1.[First Amount Date] , t2.[Sum Of GF] AS [Gift Amount] , t2.[Sum Of NP] AS [Pledge Amount] , t2.[Sum Of PP] AS [Pledge Payment Amount] , t2.[Sum Soft Credit] AS [Soft Gift Amt] , t2.[SUM SOFT PP] , t2.[SUM SOFT NP] , t2.[Sum Of GF] + t2.[Sum Soft Credit] + t2.[Sum Of PP] AS [Lifetime Giving] FROM ( SELECT PV.ID AS ID , PV.Prefix AS Prefix , PV.[First Name] AS [First Name] , PV.[Middle Name] AS [Middle Name] , PV.[Last Name] AS [Last Name] , PV.[Company Name] AS [Company Name] , PV.[Address Line 1] AS [Address Line 1] , PV.[Address Line 2] AS [Address Line 2] , PV.[Address Line 3] AS [Address Line 3] , PV.City AS City , PV.[State] AS State , PV.Zip AS Zip , PV.Country AS Country , PV.[Address Type] AS [Address Type] , PV.[Preferred Email] AS Email , PV.Cell AS Cell , PV.Home AS Home , PV.[Business Phone] , PV.[Business Phone EXT] , PV.[Reunion Class 1] AS [Reunion Class 1] , PV.[Reunion Class 2] AS [Reunion Class 2] , PV.[Reunion Class 3] AS [Reunion Class 3] , PV.[Mail Rules 1] AS [Mail Rules 1] , PV.[Mail Rules 2] AS [Mail Rules 2] , PV.[Mail Rules 3] AS [Mail Rules 3] , C.[LIFE LARGEST AMOUNT] AS [Life Largest Amount] , C.[LARGEST AMOUNT DATE] AS [Largest Amount Date] , C.[LIFE SMALLEST AMOUNT] AS [Life Smallest Amount] , C.[SMALLEST AMOUNT DATE] AS [Smallest Amount Date] , C.[LAST AMOUNT] AS [Last Amount] , C.[LAST AMOUNT DATE] AS [Last Amount Date] , C.[FIRST AMOUNT] AS [First Amount] , C.[FIRST AMOUNT DATE] AS [First Amount 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_DONOR_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_DONOR_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_DONOR_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_DONOR_DATE ELSE NULL END) [FIRST AMOUNT DATE] FROM ( SELECT COALESCE(NULLIF(CDD.CONDES_HARD_CREDIT_AMT, 0), CDD.CONDES_SOFT_CREDIT_AMT, 0) AMT , C.CONTRIB_DONOR_DATE , ROW_NUMBER() OVER ( ORDER BY COALESCE(NULLIF(CDD.CONDES_HARD_CREDIT_AMT, 0), CDD.CONDES_SOFT_CREDIT_AMT, 0) DESC ) L , ROW_NUMBER() OVER ( ORDER BY COALESCE(NULLIF(CDD.CONDES_HARD_CREDIT_AMT, 0), CDD.CONDES_SOFT_CREDIT_AMT, 0) ASC ) S , ROW_NUMBER() OVER ( ORDER BY CONTRIB_DONOR_DATE DESC ) D , ROW_NUMBER() OVER ( ORDER BY CONTRIB_DONOR_DATE ASC ) A , CONTRIB_TYPE FROM CONTRIB_DONOR CD INNER JOIN CONTRIBUTION C ON CD.COND_CONTRIBUTION = C.CONTRIBUTION_ID INNER JOIN CONTRIB_DONOR_DESIG CDD ON CDD.CONDES_CONTRIBUTION = C.CONTRIBUTION_ID INNER JOIN CONSTITUENT_LS ON CONSTITUENT_LS.CN_CONTRIB_DONORS = CD.CONTRIB_DONOR_ID AND CONSTITUENT_LS.CN_CONTRIB_DONORS = CDD.CONDES_CONTRIB_DONOR LEFT JOIN PREFERRED_MAIL_VIEW ON CD.COND_DONOR = PREFERRED_MAIL_VIEW.ID WHERE CD.COND_DONOR = PV.ID AND ( C.CONTRIB_TYPE IN ( 'GF', 'PP' ) ) ) T ) C ) AS t1 , ( SELECT PV.ID , C.[SUM SOFT NP] AS [SUM SOFT NP] , C.[SUM SOFT PP] AS [SUM SOFT PP] , CASE WHEN C.[SUM OF GIFTS] IS NOT NULL THEN C.[SUM OF GIFTS] ELSE 0 END AS [Sum Of GF] , CASE WHEN C.[SUM PAID TO DATE] IS NOT NULL THEN C.[SUM PAID TO DATE] ELSE 0 END AS [Sum Of PP] , C.[SUM NP GIFTS] AS [Sum Of NP] , CASE WHEN C.[Sum Soft Credit] IS NOT NULL THEN C.[Sum Soft Credit] ELSE 0 END AS [Sum Soft Credit] FROM PREFERRED_MAIL_VIEW PV CROSS APPLY ( SELECT 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 0 END) [SUM NP GIFTS] , SUM(CASE WHEN CONTRIB_TYPE = 'PP' THEN ammt ELSE 0 END) [SUM SOFT PP] , SUM(CASE WHEN CONTRIB_TYPE = 'NP' THEN ammt ELSE 0 END) [SUM SOFT NP] , SUM(ammt) [Sum Soft Credit] FROM ( SELECT NULLIF(CDD.CONDES_HARD_CREDIT_AMT, 0) AMT , CDD.CONDES_SOFT_CREDIT_AMT ammt , C.CONTRIB_DONOR_DATE , CONTRIB_TYPE FROM CONTRIB_DONOR CD INNER JOIN CONTRIBUTION C ON CD.COND_CONTRIBUTION = C.CONTRIBUTION_ID INNER JOIN CONTRIB_DONOR_DESIG CDD ON CDD.CONDES_CONTRIBUTION = C.CONTRIBUTION_ID INNER JOIN CONSTITUENT_LS ON CONSTITUENT_LS.CN_CONTRIB_DONORS = CD.CONTRIB_DONOR_ID AND CONSTITUENT_LS.CN_CONTRIB_DONORS = CDD.CONDES_CONTRIB_DONOR LEFT JOIN PREFERRED_MAIL_VIEW ON CD.COND_DONOR = PREFERRED_MAIL_VIEW.ID WHERE CD.COND_DONOR = PV.ID AND C.CONTRIB_TYPE IN ( 'GF', 'PP', 'NP' ) ) T ) C ) AS t2 , ( SELECT p.ID , p.SUFFIX , p.SPOUSE , p.PREFERRED_RESIDENCE , p.PERSON_STATUS , p.BIRTH_DATE , p.GENDER , p.DECEASED_DATE , p.MARITAL_STATUS FROM PERSON p ) AS t3 WHERE t1.ID = t2.ID AND t2.ID = t3.ID;