How to update running records in SQL Server

Question

Here, in the table below, I want to update the second opening of a row with closing the first row and so on. The closing column is calculated as (opening + total)

  ID    opening  Total      Closing
  ---   --------  ------------  -------------
   1    0   3015591.25  3015591.25
   2    0   2146798.4   NULL
   3    0   3015591.25  NULL
   4    0   2146798.4   NULL
   5    0   3015591.25  NULL
   6    0   2146798.4   NULL
   7    0   3015591.25  NULL
   8    0   2146798.4   NULL

And the conclusion should be like this:

       ID    opening      Total     Closing
       ---   --------   ------------  -------------
        1   0           3015591.25  3015591.25
        2   3015591.25  2146798.4   5162389.65
        3   5162389.65  3015591.25  8177981.25
        4   8177980.9   2146798.4   10324779.4
        5   10324779.3  3015591.25  13340370.25
        6   13340370.55 2146798.4   15487168.4
        7   15487168.95 3015591.25  18502759.25
        8   18502760.2  2146798.4   20649557.4

any solution on this.

Answer 1

An answer containing a loop WHILE.

--Simulated your table
DECLARE @tbl TABLE 
(
    ID INT,
    opening FLOAT, 
    Total FLOAT,
    Closing FLOAT
)

--Testing values
INSERT INTO @tbl(ID, opening , Total ,Closing) VALUES(1,0,3015591.25,3015591.25)
INSERT INTO @tbl(ID, opening , Total ,Closing) VALUES(2,0,2146798.4,NULL)
INSERT INTO @tbl(ID, opening , Total ,Closing) VALUES(3,0,3015591.25,NULL)
INSERT INTO @tbl(ID, opening , Total ,Closing) VALUES(4,0,2146798.4,NULL)
INSERT INTO @tbl(ID, opening , Total ,Closing) VALUES(5,0,3015591.25,NULL)
INSERT INTO @tbl(ID, opening , Total ,Closing) VALUES(6,0,2146798.4,NULL)
INSERT INTO @tbl(ID, opening , Total ,Closing) VALUES(7,0,3015591.25,NULL)
INSERT INTO @tbl(ID, opening , Total ,Closing) VALUES(8,0,2146798.4,NULL)

--Solution starts from here
DECLARE @StartCount INT, @TotalCount INT, @OPENING FLOAT,@CLOSING FLOAT

SELECT @TotalCount = MAX(ID) FROM @tbl;
SET @StartCount = 2;

WHILE(@StartCount <= @TotalCount)
BEGIN
    SELECT @OPENING = ISNULL(Closing, 0) FROM @tbl WHERE ID = @StartCount - 1
    SELECT @CLOSING = (@OPENING + Total) FROM @tbl WHERE ID = @StartCount

    UPDATE @tbl
    SET opening = @OPENING,
    Closing = @CLOSING
    WHERE ID = @StartCount

    SELECT @StartCount = @StartCount + 1
END

SELECT * FROM @tbl

Hope this helps

Answer 2

You can use this query based on the fact that your closing value is the sum of the totals from all previous lines (the current line is included), and the opening value is the same as not including the current line.

update table1
set
    Opening = isnull((select sum(total) from table1 t where t.ID < table1.ID), 0),
    Closing = (select sum(total) from table1 t where t.ID <= table1.ID)

SQL Fiddle, , .

, , .

Answer 3

Try the following:

select id, sum(total) over(order by id)-total as opening, total, sum(total) over(order by id) as closing
from #t