CODEWITHSUNDEEP
sqlsql-server-2008
whitz11
whitz11

Reputation: 229

Cumulative total for quarters

I currently have a query which produces data for each quarter, what I would like is for the total to be a running total. So quarter 2's figures will be added on to quarter 1's and so on until the end of the fiscal year, where the process will then start again.

select  datepart(year,date) as Year,
    CASE
    WHEN MONTH(date) BETWEEN 1  AND 3  THEN convert(char(4), YEAR(date) - 1) + 'Q4'
    WHEN MONTH(date) BETWEEN 4  AND 6  THEN convert(char(4), YEAR(date) - 0) + 'Q1'
    WHEN MONTH(date) BETWEEN 7  AND 9  THEN convert(char(4), YEAR(date) - 0) + 'Q2'
    WHEN MONTH(date) BETWEEN 10 AND 12 THEN convert(char(4), YEAR(date) - 0) + 'Q3'
END AS Quarter,

 Data1,
 Data2,

FROM TABLE

GROUP BY datepart(year,date),
    CASE
    WHEN MONTH(date) BETWEEN 1  AND 3  THEN convert(char(4), YEAR(date) - 1) + 'Q4'
    WHEN MONTH(date) BETWEEN 4  AND 6  THEN convert(char(4), YEAR(date) - 0) + 'Q1'
    WHEN MONTH(date) BETWEEN 7  AND 9  THEN convert(char(4), YEAR(date) - 0) + 'Q2'
    WHEN MONTH(date) BETWEEN 10 AND 12 THEN convert(char(4), YEAR(date) - 0) + 'Q3'
END

This is what I currently get

year    quarter     data1        data2

2016      Q1         10            4
2016      Q2         5             6
2016      Q3         4             2
2017      Q4         1             1

I would like the output to look like this

year    quarter     data1        data2

2016      Q1         10            4
2016      Q2         15            10
2016      Q3         19            12
2017      Q4         20            13

Thanks for any help

Upvotes: 2

Views: 2029

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

First, your query can be simplified to:

SELECT datepart(year, date) as Year,
       datename(year, date) + 'Q' + datename(quarter, dateadd(month, -3, date)) as quarter,
       SUM(Data1) as Data1, SUM(Data2) as Data2
FROM TABLE
GROUP BY datepart(year, date) as Year,
         datename(year, date) + 'Q' + datename(quarter, dateadd(month, -3, date)) as quarter
ORDER BY quarter;

Note the use of datename(). This returns a string rather than a number, which is easier for string concatenation. In addition, this simplifies the logic just by subtracting 3 months to get the quarter.

In SQL Server 2012+, you can use the built in functionality for cumulative sum. In SQL Server 2008, one method uses apply:

WITH t as (
      SELECT datepart(year, date) as Year,
             datename(year, date) + 'Q' + datename(quarter, dateadd(month, -3, date)) as quarter,
             SUM(Data1) as Data1, SUM(Data2) as Data2
      FROM TABLE
      GROUP BY datepart(year, date) as Year,
               datename(year, date) + 'Q' + datename(quarter, dateadd(month, -3, date)) as quarter
     )
SELECT t.*, tt.data2
FROM t OUTER APPLY
     (SELECT SUM(data1) as data2
      FROM t t2
      WHERE t2.quarter <= t.quarter
     ) tt;

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can wrap your query in a CTE and use CROSS APPLY in order to calculate the cumulative sum:

;WITH CTE AS (
   -- your query
)
SELECT t1.year, t1.quarter, t1.data1, t3.cumulative_sum
FROM CTE AS t1 
CROSS APPLY (
   SELECT SUM(data2) AS cumulative_sum
   FROM CTE AS t2
   WHERE t2.year <= t1.year AND t2.quarter <= t1.quarter) AS t3

Upvotes: 1

Related Questions