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
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.
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)
3 comments:
Wonderful stuff, it solved my problem, kudos
Thank you for this nice example that works in SQL.
What is ...
WHERE t.rownum &> 1
shouldn't it be simply ...
WHERE t.rownum > 1
Post a Comment