Running Total That Changes Each Time A Column Value Changes

Question

Good morning.

I am trying to solve an issue that is very similar to the post below, but with 1 extra parameter that is tripping me up.

SQL Server Query, running total in view, reset when column A changes

In the answer to this question, the code below was provided.

Original Code

DECLARE @T TABLE (Category VARCHAR(5), Value INT)
INSERT INTO @T VALUES 
    ('Cat A', 10),
    ('Cat A', 20),
    ('Cat A', 30),
    ('Cat B', 15),
    ('Cat B', 15),
    ('Cat C', 10),
    ('Cat C', 10)

;WITH T AS
(   SELECT  Category, Value, ROW_NUMBER() OVER(PARTITION BY Category ORDER BY Value) [RowNumber]
    FROM    @T
)
SELECT  T1.Category,
        T1.Value,
        RunningTotal
FROM    T T1
        OUTER APPLY
        (   SELECT  SUM(Value) [RunningTotal]
            FROM    T T2
            WHERE   T2.Category = T1.Category
            AND     T2.RowNumber <= T1.RowNumber
        ) RunningTotal

This solution works correctly if the categories should go in order; however if for example the order goes A, A, A, B, B, C, C, A, A, B then all the As will be grouped at the top followed by B, etc and the order will not be maintained. This can be seen below:

Extended Sample

DECLARE @T TABLE (Category VARCHAR(5), Value INT)
INSERT INTO @T VALUES 
    ('Cat A', 10),
    ('Cat A', 20),
    ('Cat A', 30),
    ('Cat B', 15),
    ('Cat B', 15),
    ('Cat C', 10),
    ('Cat C', 10),
    ('Cat A', 100),
    ('Cat A', 05),
    ('Cat B', 15)

;WITH T AS
(   SELECT  Category, Value, ROW_NUMBER() OVER(PARTITION BY Category ORDER BY Value) [RowNumber]
    FROM    @T
)
SELECT  T1.Category,
        T1.Value,
        RunningTotal
FROM    T T1
        OUTER APPLY
        (   SELECT  SUM(Value) [RunningTotal]
            FROM    T T2
            WHERE   T2.Category = T1.Category
            AND     T2.RowNumber <= T1.RowNumber
        ) RunningTotal

Sample Code Output

╔══════════╦════════╦═══════════════╗
║ Category ║ Amount ║ Running Total ║
╠══════════╬════════╬═══════════════╣
║ Cat A    ║      5 ║             5 ║
║ Cat A    ║     10 ║            15 ║
║ Cat A    ║     20 ║            35 ║
║ Cat A    ║     30 ║            65 ║
║ Cat A    ║    100 ║           165 ║
║ Cat B    ║     15 ║            15 ║
║ Cat B    ║     15 ║            30 ║
║ Cat B    ║     15 ║            45 ║
║ Cat C    ║     10 ║            10 ║
║ Cat C    ║     10 ║            20 ║
╚══════════╩════════╩═══════════════╝

Required Output

I have added one more column to this to illustrate my example. As you can see below, the table is sorted by Date so the Category stays in that order; however the Running total resets every time the Category changes.

╔══════════╦════════╦═══════════════╦════════════╗
║ Category ║ Amount ║ Running Total ║    Date    ║
╠══════════╬════════╬═══════════════╬════════════╣
║ Cat A    ║     10 ║            10 ║ 23/10/2015 ║
║ Cat A    ║     20 ║            30 ║ 17/10/2015 ║
║ Cat A    ║     30 ║            60 ║ 15/10/2015 ║
║ Cat B    ║     15 ║            15 ║ 02/10/2015 ║
║ Cat B    ║     15 ║            30 ║ 24/09/2015 ║
║ Cat C    ║     10 ║            10 ║ 17/09/2015 ║
║ Cat C    ║     10 ║            20 ║ 14/09/2015 ║
║ Cat A    ║    100 ║           100 ║ 12/09/2015 ║
║ Cat A    ║      5 ║           105 ║ 08/09/2015 ║
║ Cat B    ║     15 ║            15 ║ 03/09/2015 ║
╚══════════╩════════╩═══════════════╩════════════╝

Example When Dates Are The Same

Note that the first 2 entries have the same date.

╔══════════╦═══════╦════════════╦══════════════╗
║ Category ║ Value ║    Date    ║ RunningTotal ║
╠══════════╬═══════╬════════════╬══════════════╣
║ Cat A    ║    10 ║ 30/05/2015 ║           30 ║
║ Cat A    ║    20 ║ 30/05/2015 ║           30 ║
║ Cat A    ║    30 ║ 28/05/2015 ║           60 ║
║ Cat B    ║    15 ║ 27/05/2015 ║           15 ║
║ Cat B    ║    15 ║ 26/05/2015 ║           30 ║
║ Cat C    ║    10 ║ 25/05/2015 ║           10 ║
║ Cat C    ║    10 ║ 24/05/2015 ║           20 ║
║ Cat A    ║   100 ║ 23/05/2015 ║          100 ║
║ Cat A    ║     5 ║ 22/05/2015 ║          105 ║
║ Cat B    ║    15 ║ 21/05/2015 ║           15 ║
╚══════════╩═══════╩════════════╩══════════════╝

The number of categories can change and could be up to 100 different values.

This query will be used in an SSRS report so if anyone can help me with an SSRS example, that would be great, if not then just the SQL would also be amazing.

Many thanks,

Ninja.

Answer 1

Here is an example. First you are getting islands and then calculating running total:

DECLARE @T TABLE (Category VARCHAR(5), Value INT, Date DATE)
INSERT INTO @T VALUES 
    ('Cat A', 10, '20150530'),
    ('Cat A', 20, '20150529'),
    ('Cat A', 30, '20150528'),
    ('Cat B', 15, '20150527'),
    ('Cat B', 15, '20150526'),
    ('Cat C', 10, '20150525'),
    ('Cat C', 10, '20150524'),
    ('Cat A', 100, '20150523'),
    ('Cat A', 05, '20150522'),
    ('Cat B', 15, '20150521')

;WITH cte AS(SELECT *, ROW_NUMBER() OVER(Order By date DESC)
              - ROW_NUMBER() OVER(Partition By Category Order By date DESC) rn FROM @t)
SELECT * FROM cte c1
CROSS APPLY(SELECT SUM(c2.Value) AS RunningTotal FROM cte c2 
            WHERE c2.rn = c1.rn AND c2.Category = c1.Category AND c2.Date >= c1.Date) ca
ORDER BY c1.Date desc

Output:

Category    Value   Date        rn  RunningTotal
Cat A       10      2015-05-30  0   10
Cat A       20      2015-05-29  0   30
Cat A       30      2015-05-28  0   60
Cat B       15      2015-05-27  3   15
Cat B       15      2015-05-26  3   30
Cat C       10      2015-05-25  5   10
Cat C       10      2015-05-24  5   20
Cat A       100     2015-05-23  4   100
Cat A       5       2015-05-22  4   105
Cat B       15      2015-05-21  7   15

EDIT:

Simply add another cte above to give unambiguous ordering:

;WITH cte1 AS(SELECT *, ROW_NUMBER() OVER(Order By date DESC) rn1 FROM @T),
      cte2 AS(SELECT *, ROW_NUMBER() OVER(Order By rn1)
              - ROW_NUMBER() OVER(Partition By Category Order By rn1) rn2 FROM cte1)
SELECT * FROM cte2 c1
CROSS APPLY(SELECT SUM(c2.Value) AS RunningTotal FROM cte2 c2 
            WHERE c2.rn2 = c1.rn2 AND c2.Category = c1.Category AND c2.rn1 <= c1.rn1) ca
ORDER BY c1.Date desc