CODEWITHSUNDEEP
sqlsql-server-2012
Mike C
Mike C

Reputation: 63

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.

Upvotes: 1

Views: 462

Answers (3)

Mike C
Mike C

Reputation: 63

Ended up using a sum of LAG() unbounded. This issue became a mute issue, once scope of the project was altered dramatically and conditions no longer required a complex rolling totals, allowing for simple rolling totals (2012 introduces LAG() and LEAD() functions making rolling totals easier, in my opinion).

Thank you for the previous suggestions.

Upvotes: 0

chungtinhlakho
chungtinhlakho

Reputation: 930

Tested in and works in sql-server 2008.

declare @data table(Date date, amount float)

insert into @data
values('04/01/2016',10.00),
('04/01/2016',25.00),
('05/01/2016',10.00),
('05/01/2016',15.00),
('06/01/2016',1.00)

select Date, (select sum(amount) from @data d2 where d2.Date <=d1.Date) as runningTotal
from @data d1
group by date

in 2012, i believe you can do something like this.

    select Date, SUM(amount) over(order by Date ROWS UNBOUNDED PRECDING) as runningtotal
    from @date
group by Date

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can do this with aggregation and a cumulative sum:

select umonth, 
       sum(sum(amount)) over (order by umonth) as Totals
from data
group by umonth;

Upvotes: 5

Related Questions