get calculation result from two columns in sql server

Question

Please refer to this table below.

 |RefNbr   | DocDate     | OrigAmt    | AdjAmt     | Balances |
 |INV001   | 2016-03-15  | 5,000.00   |   250.00   | 4,750.00 |
 |INV002   | 2016-03-16  | 5,000.00   |   750.00   | 4,000.00 |
 |INV003   | 2016-03-17  | 5,000.00   | 1,000.00   | 3,000.00 | 
 |INV004   | 2016-03-19  | 5,000.00   |   500.00   | 2,500.00 |

how to provide query to get value of balances ? (Balances = OrigAmt - AdjAmt (this rule only for the first row), and then in second row, Balances = Prev Balances (balances in first row) - AdjAmt, and etc).

Answer 1

Here is one way using windowed aggregate function

select OrigAmt - sum(AdjAmt) over(order by  DocDate asc) as Balances 
From yourtable

For anything less than sql server 2012 use this

SELECT OrigAmt - cum_sum AS Balances
FROM   yourtable a
       CROSS apply (SELECT Sum(AdjAmt)
                    FROM   yourtable b
                    WHERE  b.DocDate <= a.DocDate) cs( cum_sum)