How to update columns based on previous row in SQL

Question

I have a table consisting of inventory data. The first row of the table is correct however for all the other rows, initial inventory should be the final inventory of the previous day and the final inventory should be initial inventory + recieved_sold of that date after having the correct initial inventory for that day . How can I do that in SQL?

Source

initial inventory   date    received_sold   final inventory
20                  1/1/23       -1           19
20                  1/2/23       0            20
20                  1/3/23       4            24
20                  1/4/23       2            22
20                  1/5/23       -2           18

expected:

initial inventory   date    received_sold   final inventory
20                  1/1/23       -1           19
19                  1/2/23       0            19
19                  1/3/23       4            23
23                  1/4/23       2            25
25                  1/5/23       -2           23

Answer 1

For testing purposes, I used the following setup script:

CREATE TABLE inventory (
    initial int,
    date date PRIMARY KEY,
    recsold int,
    final int
)
INSERT INTO inventory VALUES
    (20, '1/1/2023', -1, 19),
    (20, '1/2/2023', 0, 20),
    (20, '1/3/2023', 4, 24),
    (20, '1/4/2023', 2, 22),
    (20, '1/5/2023', -2, 18)

Output of SELECT * FROM inventory:

initial date        recsold  final
20      2023-01-01  -1       19
20      2023-01-02   0       20
20      2023-01-03   4       24
20      2023-01-04   2       22
20      2023-01-05  -2       18

Updating columns initial and final can be done in various ways. A simple solution is an UPDATE statement with a subquery. For each row being updated, the subquery calculates the sum of all preceding rows.

DECLARE @start int = (SELECT TOP 1 initial FROM inventory ORDER BY date)

UPDATE i
SET initial = @start + acc.recsold
    , final = @start + acc.recsold + i.recsold
FROM inventory i
CROSS APPLY (SELECT ISNULL(SUM(recsold), 0) AS recsold FROM inventory WHERE date < i.date) acc

Expect this to run in O(n²) time. That might be OK for a small table, but for a large number of rows, it may be better to use a cursor. The following loop will update the rows one by one. Performance-wise, that is still far from ideal, but its time complexity can be close to O(n). Just try and see what works best for you.

DECLARE @initial int, @date date, @recsold int, @stock int
DECLARE cur CURSOR FOR SELECT initial, date, recsold FROM inventory ORDER BY date
OPEN cur
FETCH NEXT FROM cur INTO @initial, @date, @recsold
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @stock IS NULL SET @stock = @initial
    UPDATE inventory SET initial = @stock, final = @stock + @recsold WHERE date = @date
    SET @stock += @recsold
    FETCH NEXT FROM cur INTO @initial, @date, @recsold
END
CLOSE cur
DEALLOCATE cur

Output of SELECT * FROM inventory:

initial date        recsold  final
20      2023-01-01  -1       19
19      2023-01-02   0       19
19      2023-01-03   4       23
23      2023-01-04   2       25
25      2023-01-05  -2       23

Answer 2

You can do it using row_number() to get the first row, and first_value() to get the initial final_inventory. the with sum over() we get the expected data :

with cte as (
  select *, row_number() over (order by thedate ) as id,
  first_value(final_inventory) over (order by thedate ) as first_final_inventory
  from mytable
),
cte2 as (
  select initial_inventory, thedate, received_sold, 
  sum(case when id = 1 then first_final_inventory else received_sold end) over (order by thedate) as 'new_final_inventory'
  from cte
)
select lag(new_final_inventory, 1, initial_inventory) over (order by thedate) as 'new_initial_inventory',
    thedate, received_sold, new_final_inventory
from cte2;

Without WITH :

select * from mytable t
inner join (
  select lag(new_final_inventory, 1, initial_inventory) over (order by thedate) as 'new_initial_inventory',
    thedate, received_sold, new_final_inventory
  from (
    select initial_inventory, thedate, received_sold, 
    sum(case when id = 1 then first_final_inventory else received_sold end) over (order by thedate) as 'new_final_inventory'
    from (
      select *, row_number() over (order by thedate ) as id,
      first_value(final_inventory) over (order by thedate ) as first_final_inventory
      from mytable
    ) as s2
  ) as s1
) as s on s.thedate = t.thedate

The Update query can be as follows :

update mytable
set initial_inventory = s.new_initial_inventory,
    final_inventory = s.new_final_inventory
from mytable t
inner join (
  select lag(new_final_inventory, 1, initial_inventory) over (order by thedate) as 'new_initial_inventory',
    thedate, received_sold, new_final_inventory
  from (
    select initial_inventory, thedate, received_sold, 
    sum(case when id = 1 then first_final_inventory else received_sold end) over (order by thedate) as 'new_final_inventory'
    from (
      select *, row_number() over (order by thedate ) as id,
      first_value(final_inventory) over (order by thedate ) as first_final_inventory
      from mytable
    ) as s2
  ) as s1
) as s on s.thedate = t.thedate

Demo here