Add cumulative (running) totals to TSQL query (SQL Server 2008)?

Question

I have the following query that displays weekly totals for a certain date range:

declare @from_date datetime
declare @to_date datetime

SET @from_date = '2014-03-30';    
SET @to_date = '2014-08-10';    

SELECT DATEDIFF(week, 0, IDOC.Import_Date) Week,
    DATEADD(week, DATEDIFF(week, 0, IDOC.Import_Date), 0) 'From Date',
    DATEADD(week, DATEDIFF(week, 0, IDOC.Import_Date), 0) + 6 'End Date',
    COUNT(IDOC.IDOC_ID) 'Total'
FROM IDOC    
INNER JOIN dbo.File_Type FI     
        on IDOC.File_Type_ID = FI.File_Type_ID    
INNER JOIN  IDOC_Team_Assignment ITA    
    ON IDOC.IDOC_ID=ITA.IDOC_ID    
Where IDOC.Import_Date BETWEEN @from_date AND @to_date
GROUP BY DATEDIFF(week, 0, IDOC.Import_Date) 
ORDER BY DATEDIFF(week, 0, IDOC.Import_Date)

The output:

Week    | From Date      | End Date      | Total
--------------------------------------------------
5965    2014-04-28    2014-05-04        1
5967    2014-05-12    2014-05-18      1
5968    2014-05-19    2014-05-25      2

I want to add cumulative total (running total) Here is what I want accomplish:

 Week    | From Date      | End Date      | Total | Cummulative
    -------------------------------------------------------------
    5965    2014-04-28    2014-05-04        1       1
    5967    2014-05-12    2014-05-18      1         2
    5968    2014-05-19    2014-05-25      2         4

http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver/10309947#10309947 you want a running total, not a cumulative one. The link shows you how to do this; — xQbert, May 24 '14 at 02:21
Doesn't seem straight forward with SQL Server 2008, possible without a temp table? — Chaka, May 24 '14 at 02:27

Bob · Accepted Answer · 2014-05-24T06:57:41.903

The link xQbert gave you shows you the concept, but it might get a little confusing when you have another count already in your select.

You need to remove your group by, and use over on both your counts, limiting one to just count over the group by you already had, and the second to cover the entire range.

You can't use rows unbound preceding in this case since it will create an entry for each row instead of building on the existing result set. But you do need to keep the order by in the over and at the end of the select in sync.

And you also need to add the distinct key word.

SELECT DISTINCT
    DATEDIFF(week, 0, IDOC.Import_Date) Week,
    DATEADD(week, DATEDIFF(week, 0, IDOC.Import_Date), 0) 'From Date',
    DATEADD(week, DATEDIFF(week, 0, IDOC.Import_Date), 0) + 6 'End Date',
    COUNT(IDOC.IDOC_ID) OVER (PARTITION BY DATEDIFF(week, 0, IDOC.Import_Date)) 'Total',
    COUNT(IDOC.IDOC_ID) OVER (ORDER BY DATEDIFF(week, 0, IDOC.Import_Date)) 'Running Total'
FROM IDOC    
INNER JOIN dbo.File_Type FI     
        on IDOC.File_Type_ID = FI.File_Type_ID    
INNER JOIN  IDOC_Team_Assignment ITA    
    ON IDOC.IDOC_ID=ITA.IDOC_ID    
WHERE IDOC.Import_Date BETWEEN @from_date AND @to_date
ORDER BY DATEDIFF(week, 0, IDOC.Import_Date)

I can't test this on SQL Server 2008, but I did test it in whatever comes free with VS2013 and I know it works in Oracle. It's worth a shot.

This one was tested on SQL Server 2008 R2 - I hope you aren't in a rush to get this data...

SELECT
    DATEDIFF(week, 0, IDOC.Import_Date) Week,
    DATEADD(week, DATEDIFF(week, 0, IDOC.Import_Date), 0) 'From Date',
    DATEADD(week, DATEDIFF(week, 0, IDOC.Import_Date), 0) + 6 'End Date',
    COUNT(IDOC.IDOC_ID) 'Total',
    (
        SELECT
            COUNT(aIDOC.IDOC_ID)
        FROM
            IDOC aIDOC
        INNER JOIN dbo.File_Type aFI     
            ON aIDOC.File_Type_ID = aFI.File_Type_ID    
        INNER JOIN IDOC_Team_Assignment aITA    
            ON aIDOC.IDOC_ID=aITA.IDOC_ID    
        WHERE
            aIDOC.Import_Date BETWEEN @from_date AND @to_date
            AND DATEDIFF(week, 0, IDOC.Import_Date) <= DATEDIFF(week, 0, aIDOC.Import_Date)
    ) 'RunningTotal'
FROM
    IDOC
INNER JOIN dbo.File_Type FI     
    ON IDOC.File_Type_ID = FI.File_Type_ID    
INNER JOIN  IDOC_Team_Assignment ITA    
    ON IDOC.IDOC_ID=ITA.IDOC_ID    
WHERE
    IDOC.Import_Date BETWEEN @from_date AND @to_date
GROUP BY
    DATEDIFF(week, 0, IDOC.Import_Date) 
ORDER BY
    DATEDIFF(week, 0, IDOC.Import_Date)

And now I changed that to a CTE which looks cleaner in my mind.

WITH CTE( WeekNbr, FromDate, EndDate, Total) AS (
    SELECT
        DATEDIFF(week, 0, IDOC.Import_Date) 'WeekNbr',
        DATEADD(week, DATEDIFF(week, 0, IDOC.Import_Date), 0) 'FromDate',
        DATEADD(week, DATEDIFF(week, 0, IDOC.Import_Date), 0) + 6 'EndDate',
        COUNT(IDOC.IDOC_ID) 'Total'
    FROM
        IDOC
    INNER JOIN dbo.File_Type FI     
        ON IDOC.File_Type_ID = FI.File_Type_ID    
    INNER JOIN  IDOC_Team_Assignment ITA    
        ON IDOC.IDOC_ID=ITA.IDOC_ID    
    WHERE
        IDOC.Import_Date BETWEEN @from_date AND @to_date
    GROUP BY
        DATEDIFF(week, 0, IDOC.Import_Date) 
)
SELECT
    A.WeekNbr,
    A.FromDate,
    A.EndDate,
    A.Total,
    (SELECT SUM(B.Total) FROM CTE B WHERE B.WeekNbr <= A.WeekNbr) 'RunningTotal'
FROM
    CTE A
ORDER BY
    A.WeekNbr

Thank for giving me this great example. I will need to figure out why its compaining about: COUNT(IDOC.IDOC_ID) OVER (ORDER BY DATEDIFF(week, 0, IDOC.Import_Date)) 'Running Total' — Chaka, May 24 '14 at 04:00
It could be that you can't do this in SQL Server 2008, but Microsoft says it's [okay](http://msdn.microsoft.com/en-us/library/ms189461.aspx). — Bob, May 24 '14 at 04:12
I look at the doc: http://msdn.microsoft.com/en-us/library/ms189461(v=sql.105).aspx...its not possible in 2008 — Chaka, May 24 '14 at 04:31
Yeah, I was looking at the wrong version. You can do it in the newer ones. I'm sorry. — Bob, May 24 '14 at 04:36
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/54323/discussion-between-bob-and-chaka). — Bob, May 24 '14 at 04:56
Bob, you are way too awesome! I wish I give you extra points for this...thank you so much for helping me!!!!! — Chaka, May 24 '14 at 14:55

Add cumulative (running) totals to TSQL query (SQL Server 2008)?

1 Answers1