Set a value equal to the value of a sum from a row above?

I have a table like this

I need to recalculate this due to a gap in the real table.

I know the value of the opening will be the opening + transfer of the line above but how can I do this?

I’ve tried using a cursor but can’t seem to make it work.

I’ve also tried using a while loop and setting an id to loop through each row but that doesn’t seem to work either.

>Solution :

You can use LAG along with OVER clause in SQL Server to accomplish what you’re going for.

Here is an example. Note that 1) you’ll need something to reliably order by and 2) you may need to partition the data by using PARTITION BY in addition to the ORDER BY.

CREATE TABLE #t (Id INT IDENTITY(1,1), Transfer INT);
INSERT INTO #t (Transfer)
VALUES (12), (-2) , (-2) , (-1) , (-7);

WITH withRunningTotal AS(
    SELECT t.Id, SUM(t.Transfer) OVER (ORDER BY Id) AS RunningTotal, t.Transfer 
    FROM #t t
)
SELECT w.Id, COALESCE(LAG(w.RunningTotal) OVER (ORDER BY Id),0) Opening, w.Transfer
FROM withRunningTotal w;

DROP TABLE #t;

Results:

Id          Opening     Transfer
----------- ----------- -----------
1           0           12
2           12          -2
3           10          -2
4           8           -1
5           7           -7