Aggregate derived values with SQL

Question

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

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,`? — Turophile, Aug 23 '15 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. — Jay C, Aug 23 '15 at 23:49
lad2025 your formula doesn't reliably work to place the months in their respective quarters. — Jay C, Aug 23 '15 at 23:59
Try this `[Quarter] = 'Q' + CAST(DATEPART(qq, giftdate) AS CHAR(1))` — Lukasz Szozda, Aug 24 '15 at 00: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 — Lukasz Szozda, Aug 24 '15 at 00:27

Parfait · Accepted Answer · 2015-08-28T20:51:15.597

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))

edited Aug 28 '15 at 20:51

answered Aug 24 '15 at 00:26

Parfait

104,375
17
94
125

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. – Jay C Aug 24 '15 at 04:51
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 – Jay C Aug 24 '15 at 04:57
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. – Jay C Aug 24 '15 at 05:30
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 – Jay C Aug 24 '15 at 05:38
@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](http://stackoverflow.com/questions/23730006/how-to-sum-value-of-pivoted-columns-and-add-it-into-another-pivoted-column), [2](http://stackoverflow.com/questions/11985796/pivot-dynamic-columns-no-aggregation), [3](http://stackoverflow.com/questions/22772481/how-to-pivot-unknown-number-of-columns-no-aggregate-in-sql-server). – Parfait Aug 24 '15 at 18:13
These are the gift types. I cna't get your pivot to work. New Trusts Additions to Trusts Gift Annuities Pooled Income Fund Gifts ork – Jay C Aug 27 '15 at 05:12
What is the error? Hopefully, you filled in the ellipsis (...) with original CTE. Can you post some lines of `dbo.PO_GiftDetails` or Dropbox me a sample csv file? – Parfait Aug 27 '15 at 16:03
Yes i am putting the CTE where the ellipse is. I get an error incorrect syntax referring to this line CAST(Quarter AS VARCHAR)+CAST(YEAR AS varchar) As [Period] – Jay C Aug 28 '15 at 05:24
Try `CAST([Quarter] + '-' + [Year] AS VARCHAR(100)) As [Period]`. See my edit. – Parfait Aug 28 '15 at 13:36
Your correction worked but this is not working Sum([GiftAmount) As [Grand Total] I get the error Incorrect syntax near the keyword 'As'. – Jay C Aug 28 '15 at 20:48
Missing outer square bracket for `[GiftAmount]`. See edit. – Parfait Aug 28 '15 at 20:52
The remaining error is invalid column GiftAmount. Shouldn't the brackets be unecessary since there is no space in GiftAmount. – Jay C Aug 28 '15 at 21:00
Correct. I just kept brackets for consistency with other fields, but can't figure out that remaining error. Be sure GiftAmount is specified in CTE, spelled correctly, and is a numeric field. – Parfait Aug 28 '15 at 21:09

Aggregate derived values with SQL

1 Answers1