SQL recursive calculate based on previous calculation

Question

Say we have the following SQL Server table

date, qty, debit

I want to calculate a NewDebit value based on the formula shown here. I have tried everything - grouping, partition, cte - it is just not happening...

Anything I try is either a recursive CTE, or partition by, it seems I can't get the previous sum of the calculated results.

Answer 1

See example

with  trng as( -- ordering for recursive sequentially process table 
  select *,row_number()over(order by dt)rn from test
)
,r as( -- anchor - first row
  select 0 as lvl,t.dt, qty,debit,rn
       ,cast(0.0 as float) tsum
       ,debit as newdebit
       ,cast('0' as varchar(100)) expr
       ,formula,testvalue
  from trng t
  where rn=1
  union all
  select r.lvl+1 lvl,t.dt, t.qty,r.debit,t.rn
    ,(r.newdebit+r.tsum) tsum    -- cumulative sum
    ,(r.newdebit+r.tsum)/t.qty newdebit -- partial sum
   -- for debug and clarity
    ,cast(concat(r.expr,'+',r.newdebit) as varchar(100)) expr
    ,t.formula,t.testvalue
  from r inner join trng t on t.rn=(r.rn+1)

)
select dt,qty,debit,newdebit,lvl,rn,tsum
  ,concat('(',expr,')/',qty) exprn
  ,formula,testvalue
from r

Output is

dt	qty	debit	newdebit	lvl	rn	tsum	exprn	formula	testvalue
2024-02-01	5	100	100	0	1	0	(0)/5	100	100
2024-02-02	8	100	12.5	1	2	100	(0+100)/8	100/8	12.5
2024-02-03	3	100	37.5	2	3	112.5	(0+100+12.5)/3	(100+12.5)/3	37.5
2024-02-04	6	100	25	3	4	150	(0+100+12.5+37.5)/6	(100+12.5+37.5)/6	25
2024-02-05	4	100	43.75	4	5	175	(0+100+12.5+37.5+25)/4	(100+12.5+37.5+25)/4	43.75

with test data

create table test (dt date, qty int, debit float,formula varchar(100),testValue float);
insert into test values
 ('2024-02-01',5,100,'100','100')
,('2024-02-02',8,150,'100/8',12.5)
,('2024-02-03',3, 50,'(100+12.5)/3',37.5)
,('2024-02-04',6,120,'(100+12.5+37.5)/6',25)
,('2024-02-05',4, 80,'(100+12.5+37.5+25)/4',43.75)
;