T-SQL: group values with CTE

Question

Considering the following data:

DECLARE @TBL TABLE (RecordID BIGINT, 
                    ID BIGINT, 
                    RowNumber BIGINT, 
                    Amount NUMERIC(6, 2), 
                    Balance NUMERIC (6, 2)
                   )

INSERT INTO @TBL VALUES (99, 1, 1, 10, 20)
INSERT INTO @TBL VALUES (100, 3, 1, 5, 20)
INSERT INTO @TBL VALUES (101, 1, 2, 5, 20)
INSERT INTO @TBL VALUES (102, 1, 3, 10, 20)
INSERT INTO @TBL VALUES (100, 3, 2, 50, 20)

SELECT * FROM @TBL

Assuming in this case that we have a table with this data, what I'd like to do is within each ID, for each row number ascending subtract the amount to the balance. So, my expected output for balance column should be, respectively:

My question is if there is any way of using CTE instead of a cursor to accomplish this outcome?

Sorry I didn't post any images (they show better what I'm trying to accomplish, but the forum doesn't allow it since my reputation is < 10).

possible duplicate of [Calculate running total / running balance](http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance) — Tab Alleman, Aug 24 '15 at 17:29
I could not understand the part "for each row number ascending subtract the amount to the balance". Do you want to subtract amount from balance while grouping by ID? — Abdullah Nehir, Aug 24 '15 at 17:30

score 0 · Accepted Answer · answered Aug 24 '15 at 18:20

I think I understand what you want, and yes: a recursive CTE can do it for you.

;with CTE
as (
    select RecordID, ID, RowNumber, Amount, cast(Balance - Amount as numeric(6,2)) 'Balance'
      from @TBL as Anchor
     where rownumber = 1
    union all
    select tbl.RecordID, tbl.ID, tbl.RowNumber, tbl.Amount, cast(cte.balance - tbl.amount as numeric(6,2)) 'Balance'
      from @TBL tbl
      join CTE
        on CTE.ID = tbl.ID
       and CTE.RowNumber = tbl.Rownumber - 1
   ) 
select * from CTE
order by RecordID asc

Produces:

RecordID   ID RowNumber   Amount   Balance
99          1         1    10.00     10.00
100         3         1     5.00     15.00
100         3         2    50.00    -35.00
101         1         2     5.00      5.00
102         1         3    10.00     -5.00

Damn I was close! That is exactly what I was trying to achieve, I was missing the CTE.RowNumber = tbl.Rownumber - 1 condition in order to make it work. Thank you Brian — user729400, Aug 25 '15 at 07:45

T-SQL: group values with CTE

1 Answers1