Considering this table:
create table x (id int, total int, diff int)
And this data:
[1, 100, 20]
[2, null, 30]
[3, null, -15]
[4, null, 4]
I need to calculate the "total" column according to the previous row.
That means that in the end the data should look like that:
[1, 100, 20]
[2, 120, 30]
[3, 150, -15]
[4, 135, 4]
What is the most efficient way on doing that?
OK, here's another option. Adding as a separate answer as it's a completely different approach.
The assumption with this is that there are no gaps in the IDs - this may well not be realistic, but it demonstrates the approach. If there are gaps in the IDs, then it should just take a little tweaking on the JOIN.
DECLARE @Data TABLE (ID INTEGER PRIMARY KEY, Total INTEGER, Diff INTEGER) INSERT @Data VALUES (1, 100, 20) INSERT @Data VALUES (2, NULL, 30) INSERT @Data VALUES (3, NULL, -15) INSERT @Data VALUES (4, NULL, 4) DECLARE @PreviousTotal INTEGER SELECT @PreviousTotal = Total FROM @Data WHERE ID = 1 UPDATE d SET @PreviousTotal = d.Total = @PreviousTotal + d2.Diff FROM @Data d JOIN @Data d2 ON d.ID = d2.Id + 1 SELECT * FROM @Data
I'm not too sure about performance of this tbh, so you should test that out on, but this is one way. I'm sure there are other ways, so this is one possibility. As I say, performance would be my main concern.
DECLARE @Data TABLE (ID INTEGER PRIMARY KEY, Total INTEGER, Diff INTEGER) INSERT @Data VALUES (1, 100, 20) INSERT @Data VALUES (2, NULL, 30) INSERT @Data VALUES (3, NULL, -15) INSERT @Data VALUES (4, NULL, 4) DECLARE @StartingTotal INTEGER SELECT @StartingTotal = Total FROM @Data WHERE ID = 1 UPDATE d SET d.Total = @StartingTotal + TotalDiff FROM @Data d CROSS APPLY (SELECT SUM(Diff) TotalDiff FROM @Data d2 WHERE d2.ID < d.ID) x WHERE d.Total IS NULL SELECT * FROM @Data
In the assumption that the sample result is wrong, and it should be like
marc_s posted in his comment, you can do it like this:
For the first record:
INSERT into X Values(1, 100, 20)
Then for all others (with the corresponding
diff values in the first and last column):
INSERT into X SELECT TOP 1 2, total+diff, 30 FROM X ORDER BY id desc
Not that nice, but it works.
I would normally not recommend using cursors but in this case it might be a good option. If you worry about performance you need to test the answers given here to figure out what is fastest for you. The best solution might differ depending on the number of rows in the table.
declare @T as table (id int, total int, diff int) insert into @T values (1, 100, 20) insert into @T values (2, null, 30) insert into @T values (3, null, -15) insert into @T values (4, null, 4) declare @id int declare @diff int declare @total int select @total = total from @T where id = 1 declare cT cursor for select id, diff from @T order by id open cT fetch next from cT into @id, @diff while (@@FETCH_STATUS <> -1) begin update @T set total = @total where id = @id set @total = @total + @diff fetch next from cT into @id, @diff end close cT deallocate cT select * from @T