Year to date per month

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 开发者_如何学CCompareSales

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

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.