x

T-SQL Query Help

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

asked Jun 25, 2014 at 03:54 PM in Default

avatar image

muk
440 33 35 40

You list certain scenarios where you want all gifts on the same day to be treated as one, but don't specify whether this should apply to all of your measures - can you clarify? I ask as if it should, I'd use a CTE to do this heavy lifting up front and then simply report on the aggregated dates/amounts.

Jun 26, 2014 at 04:37 AM Dave_Green ♦
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

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

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:

x2188
x1154
x1090
x451

asked: Jun 25, 2014 at 03:54 PM

Seen: 1062 times

Last Updated: Jun 26, 2014 at 04:37 AM

Copyright 2017 Redgate Software. Privacy Policy