1

I am using SQL Server 2012. I have taken this query about as far as I am able. As you can see I am deriving values with CTEs then using the derived values in a select statement. In this example this happens just once when I concatenate Quarter +Year = Period

I could use this query in a dataset for an SSRS report to achieve the groupings I want, but I'd like to know how to do it in SQL. I have struggled when I need to derive data then perform other functions on those derivations. CTEs are one way. I have used temp tables to a limited degree, but am not well versed in their usage. Here is the query. 

USE SampleDW
WITH gifts AS
(
SELECT ClientBriefName,AccountIDKey,AdventID,AccountName,AccountType,FlipProvision,FlipDate,ExpectedFlipDate,GiftCategory,GiftType,
GiftDate,GiftAmount,AssetClass,SecurityType,UserDefinedCode,

 COALESCE(
CASE WHEN MONTH(giftdate)IN(1,2,3) THEN 'Q1' ELSE NULL END,
CASE WHEN MONTH(giftdate)IN(4,5,6) THEN 'Q2' ELSE NULL END,
CASE WHEN MONTH(giftdate)IN(7,8,9) THEN 'Q3' ELSE NULL END,
CASE WHEN MONTH(giftdate)IN(10,11,12) THEN 'Q4' ELSE NULL END
) AS Quarter,
CASE WHEN AccountType IN('CORP','ENDOW','OTHER','990','DCA','PRIV-TAX') THEN 'NO' ELSE 'YES' END AS ISPG,
YEAR(Giftdate) YEAR,
CASE WHEN AccountType IN('CRUT','CGA','CRUTNI','CRAT','PIF','NQI','CRUTMU','LEAD-PCT','LEAD-FIXED','GRANTOR-OWNER','PRE-69')
THEN 'YES' ELSE 'NO' END AS LIFEINCOME
FROM Generic.dbo.PO_GiftDetails
) 
SELECT ClientBriefName,AccountIDKey,AdventID,AccountName,AccountType,FlipProvision,FlipDate,ExpectedFlipDate,GiftCategory,GiftType,
GiftDate,GiftAmount,AssetClass,SecurityType,UserDefinedCode, ISPG, QUARTER,YEAR,gifts.LIFEINCOME, CAST(Quarter AS VARCHAR)+CAST(YEAR AS varchar) AS Period 
FROM gifts

Result Set

      ClientBriefName   AccountIDKey    AdventID    AccountName AccountType FlipProvision   FlipDate    ExpectedFlipDate    GiftCategory    GiftType    GiftDate    GiftAmount  AssetClass  SecurityType    UserDefinedCode ISPG    QUARTER YEAR    LIFEINCOME  Period
Client1 103859  Jsmith  John Smith  CRUT    0   NULL    NULL    Standard Unitrusts  New Trusts  00:00.0 82300   e   lcus    1   YES Q4  2012    YES Q42012
Client1 100281  Jsmith  John Smith  CRAT    0   NULL    NULL    Additions to Trusts Additions to Trusts 00:00.0 5000    c   caus    2   YES Q4  2012    YES Q42012
Client2 100281  JaSmith Jane Smith  CRUT    0   NULL    NULL    Additions to Trusts Additions to Trusts 00:00.0 5000    c   caus    2   YES Q4  2012    YES Q42012
Client2 100281  JaSmith Jane Smith  CRAT    0   NULL    NULL    Additions to Trusts Additions to Trusts 00:00.0 2000    c   caus    2   YES Q4  2012    YES Q42012
Client1 103859  Jsmith  John Smith  CRUT    0   NULL    NULL    Standard Unitrusts  New Trusts  00:00.0 82300   e   lcus    1   YES Q1  2012    YES Q12012
Client1 100281  Jsmith  John Smith  CRAT    0   NULL    NULL    Additions to Trusts Additions to Trusts 00:00.0 5000    c   caus    2   YES Q1  2012    YES Q12012
Client2 100281  JaSmith Jane Smith  CRUT    0   NULL    NULL    Additions to Trusts Additions to Trusts 00:00.0 5000    c   caus    2   YES Q1  2012    YES Q12012
Client2 100281  JaSmith Jane Smith  CRAT    0   NULL    NULL    Additions to Trusts Additions to Trusts 00:00.0 2000    c   caus    2   YES Q1  2012    YES Q12012

What if I then want to achieve a pivot table-like output. I have used pivot in sQL before but I am not sure how I would in this query in the simplest form possible. The desired output would group by client summing the various gift types, with a sum of all sums lastly grouped by period

AdventID    Annuity Trusts  Flip Trust  Gift Annuity Pools  Net Income Unitrusts    Grand Total Period
CLIENT1 0   0   1187880.16  800 1188680.16  Q12012
CLIENT2 0   0   25014.6 500 25514.6 Q12012
CLIENT1 0   0   550123  800 550923  Q42012
CLIENT2 0   0   4851061 600 4851661 Q42012
Improve this question
5
  • Your COALESCE+CASE statement for Quarter looks excessive. Why not CASE WHEN MONTH(giftdate)IN(1,2,3) THEN 'Q1' WHEN MONTH(giftdate)IN(4,5,6) THEN 'Q2' WHEN MONTH(giftdate)IN(7,8,9) THEN 'Q3' WHEN MONTH(giftdate)IN(10,11,12) THEN 'Q4' ELSE NULL END AS Quarter,? Commented Aug 23, 2015 at 22:34
  • I didn't need the coalesce but what I'm getting at is once you start deriving data how to you reliably stage it for further derivations and transformations. In the past I used select into then started over with the new table but that is sloppy and time consuming. Commented Aug 23, 2015 at 23:49
  • lad2025 your formula doesn't reliably work to place the months in their respective quarters. Commented Aug 23, 2015 at 23:59
  • Try this [Quarter] = 'Q' + CAST(DATEPART(qq, giftdate) AS CHAR(1)) Commented Aug 24, 2015 at 0:19
  • "In the past I used select into then started over with the new table but that is sloppy and time consuming." => use CTE with multiple steps Commented Aug 24, 2015 at 0:27

1 Answer 1

Reset to default
1

Go ahead and aggregate then as you can apply aggregation on a derived table or CTE.

Below I assume GiftType are broken into specified columns of desired result shown in your post.

WITH gifts AS
(
SELECT ClientBriefName, AccountIDKey, AdventID, AccountName, AccountType, 
       FlipProvision, FlipDate, ExpectedFlipDate, GiftCategory, GiftType,
       GiftDate, GiftAmount, AssetClass, SecurityType, UserDefinedCode,

       (CASE WHEN MONTH(GiftDate) IN (1, 2 ,3) THEN 'Q1' ELSE NULL END,
        CASE WHEN MONTH(GiftDate) IN (4, 5, 6) THEN 'Q2' ELSE NULL END,
        CASE WHEN MONTH(GiftDate) IN (7, 8, 9) THEN 'Q3' ELSE NULL END,
        CASE WHEN MONTH(GiftDate) IN (10, 11, 12) THEN 'Q4' ELSE NULL END) AS  Quarter,

       CASE WHEN AccountType IN ('CORP','ENDOW','OTHER','990','DCA','PRIV-TAX') 
            THEN 'NO' 
            ELSE 'YES' END AS ISPG,
       YEAR(Giftdate) AS [YEAR],
       CASE WHEN AccountType IN ('CRUT','CGA','CRUTNI','CRAT','PIF','NQI','CRUTMU',
                                 'LEAD-PCT', 'LEAD-FIXED','GRANTOR-OWNER','PRE-69')
            THEN 'YES' 
            ELSE 'NO' END AS LIFEINCOME
       FROM GenericDW.dbo.PO_GiftDetails
) 

/* ADJUST AGGREGATES AS NEEDED */
SELECT AdventID, Sum(CASE WHEN GiftType = 'Annuity' THEN GiftAmount ELSE 0 END) As Annuity, 
       Sum(CASE WHEN GiftType = 'Trusts' THEN GiftAmount ELSE 0 END) As Trusts, 
       Sum(CASE WHEN GiftType = 'Flip Trust' THEN GiftAmount ELSE 0 END) As [Flip Trust], 
       Sum(CASE WHEN GiftType = 'Gift' THEN GiftAmount ELSE 0 END) As Gift, 
       Sum(CASE WHEN GiftType = 'Annuity Pools' 
                THEN GiftAmount ELSE 0 END) As [Annuity Pools], 
       Sum(CASE WHEN GiftType IN ('Annuity', 'Trusts', 'Flip Trust', 'Gift', 'Annuity Pools')
                THEN GiftAmount ELSE 0) As [Net Income], 
       Sum(CASE WHEN GiftType = 'Unitrusts' THEN GiftAmount ELSE 0 END) As Unitrusts, 
       Sum(GiftAmount) As [Grand Total], 
       CAST([Quarter] + '-' + [Year] AS VARCHAR(100)) As [Period]
FROM gifts
GROUP BY AdventID, CAST([Quarter] + '-' + [Year] AS VARCHAR(100))

And for a possible pivot solution

With gifts As ( ... )

SELECT AdventID, Sum([Annuity]), Sum([Trusts]), Sum([Flip Trust]), 
       Sum([Gift]), Sum([Annuity Pools]), Sum([Unitrusts]), 
       Sum([Annuity]) + Sum([Trusts]) + Sum([Flip Trust])
           + Sum([Gift]) + Sum([Annuity Pools]) As [Net Income],
       Sum([GiftAmount]) As [Grand Total], 
       CAST([Quarter] + '-' + [Year] AS VARCHAR(100)) As [Period]
FROM gifts
PIVOT (
       Sum([GiftAmount])
       For [GiftType] In
           ([Annuity], [Trusts], [Flip Trust], [Gift], [Annuity Pools], [Unitrusts])
       ) AS pivotTable
GROUP BY AdventID, CAST([Quarter] + '-' + [Year] AS VARCHAR(100))
Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

I made a mistake in my query. I accidentally included company information. Can you please remove where it says FROM K******DW.dbo.PO_GiftDetails and change it to GenericDW.dbo? I will test your query tonight.
The only thing not working here is that Period is a derived column but when I add it after grand total I get an error saying it's not a valid column to group by.. here is the SQL... CAST(Quarter AS VARCHAR)+CAST(YEAR AS varchar) as Period
I got it to work by adding the derived value for Period again in the gorup by however only ghe grand total has values the rest are 0's.
I think there are 0's because your defined values are slightly off. However there could be X number of gift types so I'd prefer not to list them individually in a case statement. I'd rather if it summed by every occurence of gifttype regardless of how many there are like a matrix report summing each time there is a new value
@JayC - I made the edits and even added a pivot solution. I do not know the different Gift Types in your data. Please adjust as needed. While my pivot determines known columns, for a dynamic pivot (unknown number of columns) see various SO posts: 1, 2, 3.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.