SQL Server: break down Running totals from mixed Set (Running Totals and Values)

Question

I got a table with the following Columns: ID, IsRunningTotal and Amount (like in the CTE of the SQL sample). The Amount represents a Value or a RunningTotal identified by the IsRunningTotal Flag.

If You wonder about the UseCase just imagine that the ID represents the month of the Year (e.g. ID:1 = Jan 2014), so the Amount for a certain Month will be given as a RunningTotal (e.g. 3000 for March) or simply as a value (e.g. 1000 for January).

So the following sample DataSet is given:

ID   IsRunTot   Amount  
1    0          1000
2    0          1000
3    1          3000
4    1          4000
5    0          1000
6    0          1000
7    1          7000
8    1          8000

Now I want to break Down the RunningTotals to get the simple values for each ID (here 1000 for each row). like:

ID   IsRunTot   Amount   Result
1    0          1000     1000
2    0          1000     1000
3    1          3000     1000
4    1          4000     1000
5    0          1000     1000
6    0          1000     1000
7    1          7000     1000
8    1          8000     1000

For now I got this Mssql Query "work in progress" (written for SQL Server 2008 R2):

WITH MySet (ID, IsRunTot, Amount)
AS
(
 SELECT 1 AS ID, 0 AS IsRunTot, 1000 AS Amount      
   UNION
 SELECT 2 AS ID, 0 AS IsRunTot, 1000 AS Amount      
   UNION
 SELECT 3 AS ID, 1 AS IsRunTot, 3000 AS Amount      
   UNION
 SELECT 4 AS ID, 1 AS IsRunTot, 4000 AS Amount      
   UNION
 SELECT 5 AS ID, 0 AS IsRunTot, 1000 AS Amount      
   UNION
 SELECT 6 AS ID, 0 AS IsRunTot, 1000 AS Amount      
   UNION
 SELECT 7 AS ID, 1 AS IsRunTot, 7000 AS Amount      
   UNION
 SELECT 8 AS ID, 1 AS IsRunTot, 8000 AS Amount      
)
, MySet2 (ID, IsRunTot, Amount, BreakDown)
AS
(
   SELECT ID, IsRunTot, Amount, Amount AS BreakDown 
   FROM MySet WHERE ID = 1                      

      UNION ALL

   SELECT A.ID, A.IsRunTot, A.Amount
   , CASE WHEN A.IsRunTot = 1 AND B.IsRunTot = 1 THEN  A.Amount - B.Amount ELSE NULL END AS    BreakDown 
   FROM MySet A
   INNER JOIN MySet B
    ON A.ID - 1 = B.ID
)
SELECT *
FROM MySet2
OPTION (MAXRECURSION 32767);

That works if the predecessor was a running Total and produces the following result:

ID   IsRunTot   Amount   BreakDown
1    0          1000     1000
2    0          1000     NULL
3    1          3000     NULL
4    1          4000     1000
5    0          1000     NULL
6    0          1000     NULL
7    1          7000     NULL
8    1          8000     1000

As you see I am missing the Breakdown-result for ID 3 and 7. How do I extend my Query to produce the desired result?

Answer 1

This solution subtracts the previous running total and all values in between.

;WITH MySet (ID, IsRunTot, Amount)
AS
(
 SELECT 1, 0, 1000
   UNION SELECT 2, 0, 1000
   UNION SELECT 3, 1, 3000      
   UNION SELECT 4, 1, 4000      
   UNION SELECT 5, 0, 1000   
   UNION SELECT 6, 0, 1000    
   UNION SELECT 7, 1, 7000
   UNION SELECT 8, 1, 8000
)
SELECT A.ID, A.IsRunTot, A.Amount
, BreakDown = CASE WHEN A.IsRunTot = 1 THEN A.Amount - 
    (SELECT SUM(B.Amount) FROM MySet B WHERE B.ID BETWEEN ISNULL(
        (SELECT MAX(C.ID) FROM MySet C WHERE C.ID < A.ID AND IsRunTot = 1)
    ,1) AND A.ID - 1) END
FROM MySet A;

Answer 2

In addition to the set based approaches presented in other answers, an option could be to use a cursor (which sometimes can perform quite well, believe it or not) like this

SET NOCOUNT ON;

DECLARE @res TABLE (ID int, IsRunTot bit, Amount int, Breakdown int);
DECLARE @id int, @IsRunTot int, @Amount int;

DECLARE _cursor CURSOR FOR 
SELECT ID, IsRunTot, Amount FROM test100 ORDER BY ID;

OPEN _cursor

FETCH NEXT FROM _cursor INTO @id, @IsRunTot, @Amount

WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT @res
    SELECT @ID, @IsRunTot, @Amount, 
       CASE WHEN @IsRunTot = 1 
         THEN @Amount - (SELECT SUM(breakdown) FROM @res WHERE id < @id)
         ELSE @Amount 
       END

    FETCH NEXT FROM _cursor INTO @id, @IsRunTot, @Amount
END 
CLOSE _cursor;
DEALLOCATE _cursor;
SELECT * FROM @res

With your sample data this would be the result:

ID          IsRunTot Amount      BreakDown 
----------- -------- ----------- --------------------
1           0        1000        1000
2           0        1000        1000
3           1        3000        1000
4           1        4000        1000
5           0        1000        1000
6           0        1000        1000
7           1        7000        1000
8           1        8000        1000

Answer 3

The following utilizes CTE to calculate the true breakdowns and running totals.

DECLARE @Data TABLE (ID INT, IsRunTot BIT, Amount INT)
INSERT @Data VALUES (
1,0,1000),(
2,0,1000),(
3,1,3000),(
4,1,4000),(
5,0,1000),(
6,0,1000),(
7,1,7000),(
8,1,8000)
; WITH CTE AS (
    SELECT TOP 1
        ID,
        IsRunTot,
        Amount,
        Amount AS RunningTotal,
        Amount AS Breakdown
    FROM @Data
    ORDER BY ID
    UNION ALL
    SELECT
        D2.ID,
        D2.IsRunTot,
        D2.Amount,
        D1.RunningTotal + D2.Amount - (CASE WHEN D2.IsRunTot = 1 THEN D1.RunningTotal ELSE 0 END),
        D2.Amount - (CASE WHEN D2.IsRunTot = 1 THEN D1.RunningTotal ELSE 0 END)
    FROM CTE D1
        INNER JOIN @Data D2
            ON D1.ID + 1 = D2.ID
)
    SELECT *
    FROM CTE

This yields output

ID          IsRunTot Amount      RunningTotal Breakdown
----------- -------- ----------- ------------ -----------
1           0        1000        1000         1000
2           0        1000        2000         1000
3           1        3000        3000         1000
4           1        4000        4000         1000
5           0        1000        5000         1000
6           0        1000        6000         1000
7           1        7000        7000         1000
8           1        8000        8000         1000

Answer 4

SQL2008 has support for SUM window aggregate functions. MSDN article

SELECT ID, IsRunTot, Amount, SUM(Amount) OVER()*IsRunTot
FROM MySet