Year from date to month

Question

I have this table called Sales:

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

Now I need to get the YTD values ​​from the Sum 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 that returned an internal SQL Server error

To get around this error, I wrote the 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 amounts for the year before the date of each month, but it takes 6 minutes to process all 4800 records. It's too slow, of course. Can someone help me in sending a request that will return the YTD amounts within an acceptable time (<30 seconds)?

Thanks,

Bean

Answer 1

try the following:

 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 extra speed, make a HIGH calculated column: MonthOfDate, which is DATEPART (mm, DateOf) and add an index to 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 do not want the computed column PERSISTED, make an indexed view.