How to do running sum using CTE - SQL Server

Many time we need to do runing sum of value.
In SQL 2oo5 we can do it with use of CTE



We have table which have monthwise data for 2 years. We want running sum monthwise , which needs to reset on start of year

create table #t

(month int ,

year int,

value int)

insert into #T values (1,2001,10)

insert into #T values (2,2001,20)

insert into #T values (3,2001,30)

insert into #T values (4,2001,40)

insert into #T values (5,2001,50)

insert into #T values (6,2001,60)

insert into #T values (7,2001,70)

insert into #T values (8,2001,80)

insert into #T values (9,2001,90)

insert into #T values (10,2001,100)

insert into #T values (11,2001,110)

insert into #T values (12,2001,120)

insert into #T values (1,2002,5)

insert into #T values (2,2002,15)

insert into #T values (3,2002,25)

insert into #T values (4,2002,35)

insert into #T values (5,2002,45)

insert into #T values (6,2002,55)

insert into #T values (7,2002,65)

insert into #T values (8,2002,75)

insert into #T values (9,2002,85)

insert into #T values (10,2002,95)

insert into #T values (11,2002,105)

insert into #T values (12,2002,115)

go

with test

as

(select * , value as running_sum from #t where month = 1

union all

select t.*, t.value + t1.running_sum from #t t inner join test t1

on t.month = t1.month+1 and t.year = t1.year where t.month > 1

)

select * from test

order by year,month

option (maxrecursion 0)

Here we can see it do running sum monthwise , and it resets as start of year.

Now if we want running sum for all data instead of reset it at start of year
we need to generate rownumber for all rows in the table.

WITH rowtest

     AS (SELECT *,

                Row_number() OVER (ORDER BY year, month) rownum

         FROM   #t),

     test

     AS (SELECT *,

                value AS running_sum

         FROM   rowtest

         WHERE  rownum = 1

         UNION ALL

         SELECT t.*,

                t.value + t1.running_sum

         FROM   rowtest t

                INNER JOIN test t1

                    ON t.rownum = t1.rownum + 1

         WHERE  t.rownum &> 1)

SELECT *

FROM   test

ORDER  BY rownum

OPTION (maxrecursion 0)