cumulative count including month and year

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

SQL Server 2008 Forums

Transact-SQL (2008)

cumulative count including month and year

Author

Topic

MrBloom
Starting Member

36 Posts

Posted - 2015-03-06 : 08:57:25

Year Month MonthName CountVolByMonth
2014 1 Jan 8
2014 2 Feb 15
2014 3 Mar 20
2014 4 Apr 13
2014 5 May 26
2014 6 Jun 9
2014 7 Jul 12
2014 8 Aug 26
2014 9 Sep 42
2014 10 Oct 29
2014 11 Nov 83
2014 12 Dec 166
2015 1 Jan 176

Hi
I have a table above which has a count of Volunteers by month. I wanted to make a cumulative count using the code below. This code however only works for 12 months and after this repeats the count. How can I accumulate into the next year as well.




SELECT     Year, Month, MonthName, CountVolByMonth,
      (SELECT     SUM(CountVolByMonth) AS EXPR1
    FROM   REPORTS.NewVolByMonth AS S
  WHERE      (Month <= M.Month)) AS TotalCount
FROM         REPORTS.NewVolByMonth AS M
ORDER BY Year, Month

Many thanks

jleitao
Posting Yak Master

100 Posts

Posted - 2015-03-06 : 14:18:34

SELECT
B.Year,
B.Month,
B.MonthName,
SUM(C.CountVolByMonth) as TotalCount
FROM NewVolByMonth as b
LEFT JOIN NewVolByMonth as c
ON CONVERT(NVARCHAR(4),b.Year) + RIGHT(100 + b.Month,2) >= CONVERT(NVARCHAR(4),c.Year) + RIGHT(100 + c.Month , 2)
GROUP BY
B.Year,
B.Month,
B.MonthName
ORDER BY 1, 2

------------------------
PS - Sorry my bad english

MrBloom
Starting Member

36 Posts

Posted - 2015-03-06 : 18:28:56

This works very well!
Thank you very much for your effort.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-07 : 04:49:15

100 * Year + Month will suffice.
No need to mix datatypes.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-09 : 12:38:54

You really don't want to do a computation either, as that will prevent SQL from using any index seeks.


SELECT     Year, Month, MonthName, CountVolByMonth,
      (SELECT     SUM(S.CountVolByMonth) AS EXPR1
    FROM   #NewVolByMonth AS S
  WHERE      (S.Year < M.Year) OR (S.Year = M.Year AND S.Month <= M.Month)) AS TotalCount
FROM         #NewVolByMonth AS M
ORDER BY Year, Month

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources