Calculate Continuous Month Sum (T-SQL)

Declare  @temptable table
(
id int identity,
fk_monthid int,	fk_yearid int
,paid_amount numeric(18,2)
)
Insert into @temptable Values(7,2012,763.00)
,(8,2012,828.00),(9,2012,3685.00),(10,2012,828.00)
,(1,2013,920.00),(2,2013,920.00),(3,2013,920.00)
,(1,2014,1190.00),(2,2014,1190.00),(4,2014,1190.00)

--SQL Server 2012, 2014
--to use sum Over()

;with mycte as (
Select * ,  
DATEADD(month, - ROW_NUMBER() OVER( ORDER BY  fk_yearid, fk_monthid),  Cast(Cast (fk_yearid as char(4))+ '-'+ Right('0'+Cast (fk_monthid as varchar(2)), 2) +'-01' as date))  AS grp
 
from  @temptable )
 

select fk_yearid, fk_monthid,paid_amount,
Cast( Case WHEN row_number() Over(partition by grp   Order By id DESC) =1 
Then sum(paid_amount) Over(partition by  fk_yearid, grp Order By id)  Else 0 End  as INT)
as total    from mycte
 
Order by fk_yearid, fk_monthid

/*
fk_yearid	fk_monthid	paid_amount	total
2012	7	763.00	0
2012	8	828.00	0
2012	9	3685.00	0
2012	10	828.00	6104
2013	1	920.00	0
2013	2	920.00	0
2013	3	920.00	2760
2014	1	1190.00	0
2014	2	1190.00	2380
2014	4	1190.00	1190

*/
 

 

http://forums.asp.net/p/2013010/5791405.aspx?p=True&t=635487891946418616&pagenum=1

Advertisements

---