Sum of cummulative months - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sum of cummulative months

Author

Topic

sqlr2
Starting Member

4 Posts

Posted - 2014-08-02 : 00:38:09

Hi All,
How to calculate the cummulative of months.

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
321 394 571 577 617 692 924 944 956 1010 1308 1686

if i execute my query in this month(getdate) then it should sum from Jan to Aug
Similarly i execute same query it should sum from Jan to Sept So on.

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-08-02 : 05:38:50

[code]
DECLARE @Year int = 2013

SELECT SUM(CASE WHEN DateField < DATEADD(mm,((@Year-1900)*12) +1,0) THEN YourField ELSE 0 END) AS [Jan],
SUM(CASE WHEN DateField < DATEADD(mm,((@Year-1900)*12) +2,0) THEN YourField ELSE 0 END) AS [Feb],
SUM(CASE WHEN DateField < DATEADD(mm,((@Year-1900)*12) +3,0) THEN YourField ELSE 0 END) AS [Mar],
SUM(CASE WHEN DateField < DATEADD(mm,((@Year-1900)*12) +4,0) THEN YourField ELSE 0 END) AS [Apr]
...
SUM(CASE WHEN DateField < DATEADD(mm,((@Year-1900)*12) +10,0) THEN YourField ELSE 0 END) AS [Oct],
SUM(CASE WHEN DateField < DATEADD(mm,((@Year-1900)*12) + 11,0) THEN YourField ELSE 0 END) AS [Nov],
SUM(CASE WHEN DateField < DATEADD(yy,(@Year-1899),0) THEN YourField ELSE 0 END) AS [Dec]
FROM YourTable
WHERE Datefield >= DATEADD(yy,@Year-1900,0)
AND Datefield < DATEADD(yy,@Year-1899,0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources