CODEWITHSUNDEEP
sql-servert-sqlcommon-table-expressionsql-server-2014
Cool Dev
Cool Dev

Reputation: 19

Recursively calculate total value of current row using previous row value SQL Server

Assuming I have a table like this

 Id     Initial Value    CardCount  Total
 1      5                1          null
 1      0                2          null
 1      0                4          null
 2      10               0          null
 2      0                3          null
 2      0                1          null

And I want to update the total column to be the sum of the "total" value of the previous row + the current row's cardCount. For the first record in each Id, the total is the InitialValue + CardCount. I want the result to be like this:

 Id     Initial Value    CardCount  Total
 1      5                1          6
 1      0                2          8
 1      0                4          12
 2      10               0          10
 2      0                3          13
 2      0                1          14

I have tried this but it does not produce the correct result

declare @tmp Table(Id int, InitialValue int, CardCount int, total int null)
Insert into @tmp values (1, 5, 1, null)
Insert into @tmp values (1,  0, 2, null)
Insert into @tmp values (1,  0, 4, null)
Insert into @tmp values (2, 10, 0, null)
Insert into @tmp values (2,  0, 3, null)
Insert into @tmp values (2,  0, 1, null)

Update @tmp Set Total = InitialValue + CardCount

;with TmpTb as(
select 
    ROW_NUMBER() over (order by ID) RNum, * 
From 
    @tmp)

update c set Total= x.Total + CardCount
from TmpTb c join
(select a.RNum, a.ID, b.Total
From TmpTb a LEFT JOIN TmpTb b on a.RNum=b.RNum+1
where a.ID=b.ID )x on c.RNum>=x.RNum AND c.ID=x.ID

select * from @tmp

Upvotes: 0

Views: 2477

Answers (1)

jpw
jpw

Reputation: 44881

You don't need to use recursion for this if you are using a modern version of SQL Server that can do aggregation as window functions. Then you can do sum() as a window function:

update t1 set total = y.total
from (select *, rn = ROW_NUMBER() over (order by id) from @tmp) t1
join (
  select id, rn, sum(initialvalue+cardcount) over (partition by id order by rn) as total
  from (select *, rn = ROW_NUMBER() over (order by id) from @tmp) x
) y
on t1.rn = y.rn

With your sample data the result would be:

id  initialvalue    CardCount   total
1   5               1           6
1   0               2           8
1   0               4           12
2   10              0           10
2   0               3           13
2   0               1           14

This might work - or it might not. This code relies on the server returning the rows in the same order every time which isn't something you can rely on, but as your data doesn't seem to have anything to determine the order this might be the best you can expect. If you want to produce a running total you really need a stable attribute to order the data by in a deterministic fashion.

The main point is that if you can't order the data you can't achieve what you want with certainty.

Upvotes: 3

Related Questions