x

query help

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!!!!
more ▼

asked Jul 06, 2012 at 04:34 PM in Default

muk gravatar image

muk
400 32 34 37

@muk, how about giving your questions titles that describe the problem you are having? That way people can easily see if they can help you without having to open every question... You may get better and faster answers too :)
Jul 06, 2012 at 08:50 PM Fatherjack ♦♦
lol not a bad idea.....
Jul 06, 2012 at 08:53 PM muk
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
Look up CROSS APPLY and OUTER APPLY - you can use a query to get that information without too much trouble and it's often more efficient than trying to use a function to display those names.
more ▼

answered Jul 12, 2012 at 02:46 PM

paschott gravatar image

paschott
20 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x597
x375

asked: Jul 06, 2012 at 04:34 PM

Seen: 775 times

Last Updated: Jul 12, 2012 at 02:46 PM