Calculate Continuous Month Sum (T-SQL)
Posted: October 13, 2014 Filed under: Uncategorized Leave a commentDeclare @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