How to implement a recursive calculation on the same column?

Question

I have the following table:

ID	Transaction	Amount	Inventory	Price
1	NULL	NULL	11	NULL
2	Sale	-1	10	100
3	Purchase	2	12	102
4	Sale	-2	10	103

The first line is the starting amount and the following three lines show transactions that change the inventory. I need to calculate a new running average price based on purchases and copy the current average price in case of sales based on the following formula:

    If Transaction = NULL (i.e. starting line) then Average = 90;

    If Transaction = 'Sale' then Average = lag(Average) (i.e. whatever is the latest calculated average);

    If transaction = 'Purchase' then ((Inventory - Amount) * lag(Average) 
                                 + Amount * Price)
                                 / Inventory

Sorting order is the ID column ascending.

The problem results from the lag(average), as every calculation step requires the previous row to be the updated value, i.e. the calculation must run and update row by row.

The result table should look like this:

ID	Transaction	Amount	Inventory	Price	Average
1	NULL	NULL	11	NULL	90
2	Sale	-1	10	100	90
3	Purchase	2	12	102	92
4	Sale	-2	10	103	92

The calculation :

ID 1 --> 90 (starting value)

ID 2 --> 90 (copy previous average)

ID 3 --> 92 = ((12 - 2) * 90 + (2 * 102)) / 12

ID 4 --> 92 (copy previous average)

I have tried the following:

1. Use a column (Average) with the start value 90 and run the calculation in another column (Average_f).

    Select *, 
       case when [transaction] is null then Average
            when [transaction]  = 'Sale' then lag(Average) over (order by ID)
            when [transaction] = 'Purchase' 
                 then (((Inventory - Amount) * lag(Average) over (order by ID))
                      + (Amount * Price)) / Inventory
        end as Average_f
from table

Didn't work out:

ID	Transaction	Amount	Inventory	Price	Average	Average_f
1	NULL	NULL	11	NULL	90	90
2	Sale	-1	10	100	NULL	90
3	Purchase	2	12	102	NULL	NULL
4	Sale	-2	10	103	NULL	NULL

2. I have also tried with an update statement:

    update table
    set average = case when [transaction] is null then Average
             when [transaction] = 'Purchase' 
                 then (((Inventory - Amount) * (select lag(Average) over (order by ID)
                                                from table t 
                                                where t.ID = table.ID))
                      + (Amount * Price)) / Inventory
             when [transaction]  = 'Sale' then (select lag(Average) over (order by ID)
                                                from table t 
                                                where t.ID = table.ID)
             end

Didn't work either:

ID	Transaction	Amount	Inventory	Price	Average
1	NULL	NULL	11	NULL	90
2	Sale	-1	10	100	NULL
3	Purchase	2	12	102	NULL
4	Sale	-2	10	103	NULL

Is there a way in SQL to calculate each row individually or any other way to calculate the average using the previous average?

Answer 1

The set-based solution that takes into account nonsequential identifiers and removes the restriction on recursion levels.

with
  t1 as (
    select *
    from (
      values
        (1, NULL, NULL, 11, NULL),
        (2, 'Sale', -1, 10, 100),
        (3, 'Purchase', 2, 12, 102),
        (4, 'Sale', -2, 10, 103)
    ) as t(id, "Transaction", amount, inventory, price)
  ),
  t2 as (
    select
      *,
      lead(id) over(order by id) as next_id
    from t1
  ),
  r as (
    select *, 90 as average
    from t2
    where "Transaction" is null
    union all
    select
      t2.*,
      case t2."Transaction"
        when 'Sale' then r.average
        else (r.Average * (t2.Inventory - t2.Amount) +
              t2.Amount * t2.Price) / t2.Inventory
      end
    from r
    join t2
    on t2.id = r.next_id
  )
select
  id, "Transaction", amount, inventory, price, average
from r
OPTION (MAXRECURSION 0)
;

db<>fiddle

The cursor-based solution for comparison with a set-based solution.

drop table if exists #data;

create table #data (
  id int,
  "Transaction" nvarchar(10),
  amount int,
  inventory int,
  price int
);

insert into #data(id, "Transaction", amount, inventory, price)
  values
    (1, NULL, NULL, 11, NULL),
    (2, 'Sale', -1, 10, 100),
    (3, 'Purchase', 2, 12, 102),
    (4, 'Sale', -2, 10, 103);

drop table if exists #averages;

create table #averages (
  id int,
  "Transaction" nvarchar(10),
  amount int,
  inventory int,
  price int,
  average float
);

declare
  @id int,
  @tran nvarchar(10),
  @amt int,
  @inv int,
  @price int,
  @avg float;

DECLARE C CURSOR FORWARD_ONLY STATIC READ_ONLY FOR
  select *
  from #data
  order by id;

open c;

FETCH NEXT FROM C INTO @id, @tran, @amt, @inv, @price;

WHILE @@FETCH_STATUS = 0 BEGIN
  set @avg = case
               when @tran is null then 90
               else
                 case @tran
                   when 'Sale' then @avg
                   else (@avg * (@inv - @amt) +
                         @amt * @price) / @inv
                 end
             end;
  insert into #averages(id, "Transaction", amount, inventory, price, average)
    values(@id, @tran, @amt, @inv, @price, @avg);
  FETCH NEXT FROM C INTO @id, @tran, @amt, @inv, @price;
END;

CLOSE C;
DEALLOCATE C;

select * from #averages;

https://dbfiddle.uk/m3nePu26

The result of both solutions:

+----+-------------+--------+-----------+-------+---------+
| id | Transaction | amount | inventory | price | average |
+----+-------------+--------+-----------+-------+---------+
|  1 | null        | null   |        11 | null  |      90 |
|  2 | Sale        | -1     |        10 | 100   |      90 |
|  3 | Purchase    | 2      |        12 | 102   |      92 |
|  4 | Sale        | -2     |        10 | 103   |      92 |
+----+-------------+--------+-----------+-------+---------+