Error in calculating the current amount (cumulative compared to previous periods)

Question

I have a table, let it have My_Table , which has a Created datetime column (in SQL Server), which I am trying to print a report that historically shows how many rows My_Table for months a specific time. Now I know that I can show how many of them were added every month:

 SELECT YEAR(MT.Created), MONTH(MT.Created), COUNT(*) AS [Total Added] FROM My_Table MT GROUP BY YEAR(MT.Created), MONTH(MT.Created) ORDER BY YEAR(MT.Created), MONTH(MT.Created) 

That would return something like:

 YEAR MONTH Total Added ----------------------------- 2009 01 25 2009 02 127 2009 03 241 

However, I want to get the total size of the list for a given period (name it what you will, current amount, total amount, historical report):

  YEAR MONTH Total Size ----------------------------- -- 2008 12 325 2009 01 350 2009 02 477 2009 03 718 

I am trying to do this:

 SELECT YEAR(MT.Created) , MONTH(MT.Created) ,( SELECT COUNT(*) FROM My_Table MT_int WHERE MT_int.Created BETWEEN CAST('2009/01/01' AS datetime) AND DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MT.Created)+1,0)) -- the last day of the current month -- (Additional conditions can go here) ) AS [Total added this month] FROM My_Table MT WHERE MT.Created > CAST('2009/01/01' AS datetime) GROUP BY YEAR(MT.Created), MONTH(MT.Created) ORDER BY YEAR(MT.Created), MONTH(MT.Created) 

However, SQL Server responds with this error:

 Msg 8120, Level 16, State 1, Line 1 Column 'My_Table .Created' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 

I just know that I am missing something obvious, but, having left and returned, looking at him for a while, I am at a loss. Therefore, if someone were so kind as to indicate that I am missing (or point me to the best way to do this), I would always be grateful.

Answer 1

"Run" is meant line by line. Thus, one way is to add up the previous months and add it to the current month. To deal with the borders of the year, you also accept the minimum / maximum date for each group. CROSS APPLY is a bit RBAR, but makes it clear (what?) What is happening.

 ;WITH cTE AS ( SELECT MIN(Created) AS FirstPerGroup, MAX(Created) AS LastPerGroup, YEAR(MT.Created) AS yr, MONTH(MT.Created) AS mth, COUNT(*) AS [Monthly Total Added] FROM MY_Table MT GROUP BY YEAR(MT.Created), MONTH(MT.Created) ) SELECT C1.yr, c1.mth, SUM(C1.[Monthly Total Added]), ISNULL(PreviousTotal, 0) + SUM(C1.[Monthly Total Added]) AS RunningTotal FROM cTE c1 CROSS APPLY (SELECT SUM([Monthly Total Added]) AS PreviousTotal FROM cTE c2 WHERE c2.LastPerGroup < C1.FirstPerGroup) foo GROUP BY C1.yr, c1.mth, PreviousTotal ORDER BY C1.yr, c1.mth 

Answer 2

You are in 2005 or later, you can break it using CTE

 WITH CTE AS ( SELECT YEAR(MT.Created) as Yr , MONTH(MT.Created) as Mth ,( SELECT COUNT(*) FROM My_Table MT_int WHERE MT_int.Created BETWEEN CAST('2009/01/01' AS datetime) AND DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MT.Created)+1,0)) -- the last day of the current month -- (Additional conditions can go here) ) AS Total FROM My_Table MT WHERE MT.Created > CAST('2009/01/01' AS datetime)) SELECT Yr, Mth, SUM(Total) as Total FROM CTE GROUP BY Yr, Mth ORDER BY Yr, Mth 

Answer 3

You can take the aggregate from the final query with something like this:

 WITH CTE AS (SELECT DISTINCT YEAR(MT.Created) AS [Year] , MONTH(MT.Created) AS [Month] FROM My_Table MT WHERE MT.Created > CAST('2009/01/01' AS datetime) ) SELECT MT.[Year] , MT.[Month] ,( SELECT COUNT(*) FROM My_Table MT_int WHERE MT_int.Created >= CAST('2009/01/01' AS datetime) AND (YEAR(MT_int.Created) < MT.[Year] OR (YEAR(MT_int.Created) = MT.[Year] AND MONTH(MT_int.Created) <= MT.[Month]) ) -- the last day of the current month -- (Additional conditions can go here) ) AS [Total added this month] FROM CTE MT ORDER BY MT.[Year], MT.[Month] 

I think this should cover all past orders in the previous year or previous month in the same year, along with all orders in this month.

Answer 4

Call it a slow way, but you can do it with a function. Do not do this if My_Table is large.

 Create Function [dbo].[RunningTotal](@Yr int, @Mnth int) Returns int AS BEGIN Declare @RC int Select @RC=count(*) From My_Table Where Year(Created)<@Yr or (Year(Created)=@Yr and Month(Created) <= @Mnth) Return @RC END