Error When Calculating A Running Total (cumulative Over The Previous Periods)

I have a table, let's call it My_Table that has a Created datetime column (in SQL Server) that I'm trying to pull a report that shows historically how many rows were to My_Table by

Solution 1:

"Running" implies row by row. So one way is to sum previous months and add it to current month. To deal with year boundaries, you also take min/max date per group. The CROSS APPLY is slightly RBAR but makes it clear(er?) 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

Solution 2:

Are you on 2005 or later, You can break this using a CTE

WITH CTE AS (
SELECTYEAR(MT.Created) as Yr 
    , MONTH(MT.Created) as Mth
    ,( 
    SELECTCOUNT(*) FROM My_Table MT_int 
    WHERE MT_int.Created BETWEENCAST('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
GROUPBY Yr, Mth 
ORDERBY Yr, Mth 

Solution 3:

You could take the aggregate out of the final query with something like this:

WITH CTE AS 
(SELECTDISTINCTYEAR(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]
    ,(
    SELECTCOUNT(*) 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]
                ANDMONTH(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
ORDERBY MT.[Year], MT.[Month]

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

Solution 4:

Call it the slow way, but you could do it with a function. Don't do it if My_Table is big.

CreateFunction [dbo].[RunningTotal](@Yrint, @Mnthint)
 ReturnsintASBEGINDeclare@RCintSelect@RC=count(*)
 From My_Table
 WhereYear(Created)<@Yror (Year(Created)=@YrandMonth(Created) <=@Mnth)

 Return@RCEND