Search code examples
sqlsql-servert-sqlwindow-functions

Subtract current row value by previous row value and get balance amount to current row

I need to get the balanceAmount by subtracting the current row value from the previous row's value. Expected result is as below.

expected result

Here is my current query

select 
    pp.*,
    pp.topupAmount - sum(pp.chargeAmount) over (over by pp.ROW_NUM rows unbounded preceding) AS balanceAmount
from 
    (select 
         row_number() over (order by ppc.sortDate) ROW_NUM, ppc.*
     from 
         (select 0 as topupAmount, t1.chargeAmount, t1.sortDate  
          from t1
          union all
          select t2.topupAmount, 0 as chargeAmount, t2.sortDate   
          from t2) as ppc
     ) as pp    
order by 
    pp.ROW_NUM

This is what I am getting from above query

current result

How could I achieve this?

Solution

  • You can use window functions:

    select
    t.*,
    sum(topupAmount - chargeAmount) over(order by row_num) balanceAmount
from mytable t

    Actually by looking at your query it seems like row_num is a generated column, so you likely want:

    select
    t.*,
    sum(topupAmount - chargeAmount) over(order by sortDate) balanceAmount
from mytable t