SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Stock
(
  ID int identity,
  QUANTITY int,
  TOTAL_PRICE decimal(10,2)
);
insert into Stock (QUANTITY, TOTAL_PRICE) 
values ( 10,  100),
       ( 10,  200),
       (-10, -300),
       ( 10,  100);
4 rows affected

with StockCost as
(
      select ID, QUANTITY, TOTAL_PRICE, 
             BAL_QTY = QUANTITY,
             UNIT_COST = convert(decimal(10,2), TOTAL_PRICE / QUANTITY)
      from   Stock
      where  ID = 1

      union all

      select s.ID, s.QUANTITY, s.TOTAL_PRICE,
             BAL_QTY = c.BAL_QTY + s.QUANTITY,
             UNIT_COST = convert(decimal(10,2), 
                                 CASE WHEN u.UNIT_COST IS NULL
                                      OR   u.UNIT_COST = 0 
                                      THEN c.UNIT_COST 
                                      ELSE u.UNIT_COST 
                                      END)
      from   Stock s
             inner join StockCost c on S.ID = c.ID + 1
             cross apply
             (
                 select UNIT_COST = ((c.UNIT_COST * BAL_QTY) + s.TOTAL_PRICE) 
                                  / NULLIF((c.BAL_QTY + S.QUANTITY), 0)
             ) u
)
select *
from   StockCost


ID
QUANTITY
TOTAL_PRICE
BAL_QTY
UNIT_COST


1
10
100.00
10
10.00

2
10
200.00
20
15.00

3
-10
-300.00
10
15.00

4
10
100.00
20
12.50

ID	QUANTITY	TOTAL_PRICE	BAL_QTY	UNIT_COST
1	10	100.00	10	10.00
2	10	200.00	20	15.00
3	-10	-300.00	10	15.00
4	10	100.00	20	12.50