Year to date per month

Question

Hi all,

I have this table called Sales:

ID Date       DepartmentID Amount
1  10-12-2009           12     10
2  18-01-2010            3     23 
3  08-02-2010            4      7
...

Now I need to retrieve YTD values from the Amount column for each month and department.

First I tried this query:

SELECT MonthSales.[Month], MonthSales.DepartmentID,

    (SELECT SUM(SalesAmount.Amount) FROM Sales AS SalesAmount
        WHERE (SalesAmount.[Date] >= DATEADD(Month, -12, MonthSales.[Month]) 
            AND SalesAmount.[Date] < DATEADD(Month, 1, MonthSales.[Month]))
        AND SalesAmount.DepartmentID = MonthSales.DepartmentID) AS Amount

FROM (SELECT dateadd(month, datediff(month, 0, [Date]),0) AS [Month], DepartmentID
    FROM Sales) AS MonthSales
GROUP BY MonthSales.[Month], MonthSales.DepartmentID

But this returned Internal SQL Server error

To bypass this error I wrote following query:

SELECT CompareSales.StartDate, CompareSales.EndDate, CompareSales.DepartmentID,

    (SELECT SUM(SalesAmount.Amount) FROM Sales AS SalesAmount
        WHERE (SalesAmount.[Date] >= CompareSales.StartDate AND SalesAmount.[Date] < 
            DATEADD(Month, 1, CompareSales.EndDate))
        AND SalesAmount.DepartmentID = CompareSales.DepartmentID) AS Amount

FROM (SELECT DATEADD(Month, -12, PeriodSales.EndDate) AS StartDate,    
    PeriodSales.EndDate, PeriodSales.DepartmentID
    FROM (SELECT DISTINCT bms.DATESERIAL(DATEPART(Year, EndSales.[Date]), DATEPART 
        (Month, EndSales.[Date]), 1) AS EndDate, EndSales.DepartmentID
        FROM Sales AS EndSales) AS PeriodSales) AS CompareSales

GROUP BY CompareSales.StartDate, CompareSales.EndDate, CompareSales.DepartmentID
ORDER BY CompareSales.StartDate

This query returned the correct year to date amounts for each month, but needed 6 minutes to process all 4800 records. This is too slow of course. Could anyone help me in the direction of a query that will return the YTD amounts within an acceptable time (< 30 seconds)?

Thanks,

Bob

Answer 1

try this:

DECLARE @YourTable table (RowID int, DateOf datetime, DepartmentID int, Amount int)

INSERT INTO @YourTable VALUES (1,'12-10-2009',12,10) --changed dd-mm-yyyy to mm-dd-yyyy so it would work on my system
INSERT INTO @YourTable VALUES (2,'01-18-2010', 3,23)
INSERT INTO @YourTable VALUES (3,'02-08-2010', 4, 7)

SELECT
    DATEPART(mm,DateOf) AS MonthOf,DepartmentID,SUM(Amount) AS TotalAmount
    FROM @YourTable
    WHERE DateOf>='01-01-2010' AND DateOF<'01-01-2011'
    GROUP BY DATEPART(mm,DateOf),DepartmentID

OUTPUT

MonthOf     DepartmentID TotalAmount
----------- ------------ -----------
1           3            23
2           4            7

(2 row(s) affected)

if you still need more speed, make a PERSISTED computed column: MonthOfDate that is DATEPART(mm,DateOf) and add an index on it:

ALTER TABLE YourTable ADD MonthOfDate AS DATEPART(mm,DateOf) PERSISTED 

CREATE NONCLUSTERED INDEX IX_YourTable_MonthOfDate 
ON YourTable (MonthOfDate)

or even:

CREATE NONCLUSTERED INDEX IX_YourTable_MonthOfDate 
ON YourTable (DateOf,MonthOfDate)

if you don't want a PERSISTED computed column, make an indexed view.