sqlcommon-table-expressioncalculated-columnssql-server-express

SQL recursive calculate based on previous calculation


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

enter image description here

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


Solution

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