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
Go to Top of Page

MrBloom
Starting Member

36 Posts

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


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

Go to Top of Page

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
Go to Top of Page

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

Go to Top of Page
   

- Advertisement -