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.
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)
;
