SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable (
  initial_inventory int,
  thedate date,
  received_sold int,
  final_inventory int
);

insert into mytable values
(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);
5 rows affected

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;


new_initial_inventory
thedate
received_sold
new_final_inventory


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


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 s


new_initial_inventory
thedate
received_sold
new_final_inventory


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


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


5 rows affected

select *
from mytable;


initial_inventory
thedate
received_sold
final_inventory


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
new_initial_inventory	thedate	received_sold	new_final_inventory
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