Improve Sum and Rank performance in MS SQL

Question

I have an MS SQL table which is indexed by Item Number and Date. I need to calculate the total sum of sales for each Item Number in a range of dates (for several date ranges), like so:

DECLARE @BeginDate DATE = '2014-06-11'
DECLARE @EndDate DATE = '2014-06-11'

INSERT INTO @Ranks
SELECT 
[ItemNumber],
Sum([Sales]),
row_number OVER (ORDER BY SUM([Sales]) DESC) AS [Rank]
WHERE [Date] BETWEEN @BeginDate AND @EndDate
GROUP BY ItemNumber

-- Some logic here (not a significant effect on performance)
DELETE FROM @Ranks

SET @BeginDate = '2014-05-11'
INSERT INTO @Ranks
SELECT 
[ItemNumber],
Sum([Sales]),
row_number OVER (ORDER BY SUM([Sales]) DESC) AS [Rank]
WHERE [Date] BETWEEN @BeginDate AND @EndDate
GROUP BY ItemNumber

-- Some logic here (not a significant effect on performance)
DELETE FROM @Ranks

-- Query runs several more times, at three, six, and twelve months

First question: is there a way I can refactor this so it only takes one query? Second, is there a better way to calculate ranking by sum? My database is large enough that this is taking several minutes to run.

Answer 1

I think you want conditional aggregation, so you should be able to do all this in one query:

select t.*,
       row_number() over (order by sales_1 desc) as rank_1,
       row_number() over (order by sales_2 desc) as rank_2,
       row_number() over (order by sales_3 desc) as rank_3,
       row_number() over (order by sales_4 desc) as rank_4
from (SELECT ItemNumber,
             Sum(case when [date] between @BeginDate1 and @EndDate1 then Sales end) as Sales_1, 
             Sum(case when [date] between @BeginDate2 and @EndDate2 then Sales end) as Sales_2,
             Sum(case when [date] between @BeginDate3 and @EndDate3 then Sales end) as Sales_3,
             Sum(case when [date] between @BeginDate4 and @EndDate4 then Sales end) as Sales_4
      WHERE [Date] BETWEEN @BeginDate AND @EndDate
      GROUP BY ItemNumber
     ) t