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.
精彩评论