mardi 14 juin 2016

tSQL - Recursive Running Total

I have some data that is sorted by month. What I am trying to do is get a running total; however the total of the current month is dependent on the total of the previous month. IE (Total of current Month) + (total of previous month). The data that I have it like so:

DATE      Amount
04/2016    10.00
04/2016    25.00
05/2016    10.00
05/2016    15.00
06/2016    1.00
...

So that totals would should result as such

DATE      Totals
04/2016    35.00
05/2016    60.00
06/2016    61.00
...

Lets say the schema is umonth, amount in table data. I am trying to figure this out

Select umonth, LAG(SUM(Amount)) Over (Partition By umonth Order by umonth) Total from data

I know this will not work as the Sum(Amount) is not a total of the sum + total of the previous month. HELP. Is this a case for a stored procedure? So that I can set the @prevTotal to calculate into the @currTotal with a cursor - excuse my foul language.

This is on a MSSQL 2012 database.

Aucun commentaire:

Enregistrer un commentaire