question

Hello, This query is an ongoing project in case anyone has been following my posts: 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.[Address Type], t1.Email, t1.Cell, t1.Home, 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] 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.[Address Type] AS [Address Type], PV.[Preferred Email] AS Email, PV.Cell AS Cell, PV.Home AS Home, 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_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 COALESCE (NULLIF (CDD.CONDES_HARD_CREDIT_AMT, 0), CDD.CONDES_SOFT_CREDIT_AMT, 0) AMT, C.CONTRIB_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_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', 'PP'))) T) C) AS t1, (SELECT PV.ID, C.[SUM SOFT NP] AS [SUM SOFT NP], C.[SUM SOFT PP] AS [SUM SOFT PP], C.[SUM OF GIFTS] AS [Sum Of GF], C.[SUM PAID TO DATE] AS [Sum Of PP], C.[SUM NP GIFTS] AS [Sum Of NP], C.[Sum Soft Credit] 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_DATE, 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) 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 Now I just need to add Spouse first, last, and middle name. This is identified by spouse ID from person table (the spouse id is the same as ID in person table) Any input would be super helpful thanks!!!!