Summarise by week, even for empty rows

Question

I want to summarise the sum of sales.quantity by week, and to show the week number even if there are no sales.

I have setup a weeks table with 1-54 in there to use an outer join to force all week numbers to come through, but it isn't working. It misses out weeks where there have been no sales.

My query is:

SELECT Weeks.WeekNum, SUM(sales.quantity) AS sales
FROM Weeks LEFT OUTER JOIN 
     sales ON Weeks.WeekNum = DATEPART(week, sales.transDate)
WHERE (sales.transDate BETWEEN @fromDate AND @toDate)
GROUP BY Weeks.WeekNum

ANY help would be greatly received... it's probably something stupid that I've done!

score 2 · Accepted Answer · answered Jan 17 '12 at 13:55

The where clause WHERE (sales.transDate BETWEEN @fromDate AND @toDate) will remove any weeks without sales. You'll likely need to do a subquery to pull the transactions and then join that to your weeks table.

SELECT Weeks.WeekNum, SUM(sales.quantity) AS sales
FROM Weeks LEFT OUTER JOIN 
 (
    SELECT *
    FROM sales 
    WHERE (sales.transDate BETWEEN @fromDate AND @toDate)
 ) sales
    ON Weeks.WeekNum = DATEPART(week, sales.transDate)
GROUP BY Weeks.WeekNum

score 1 · Answer 2 · answered Jan 17 '12 at 14:05

1

I prefer this method versus a sub-select

SELECT Weeks.WeekNum, SUM(sales.quantity) AS sales
FROM Weeks
LEFT OUTER JOIN  sales ON (Weeks.WeekNum = DATEPART(week, sales.transDate) AND sales.transDate BETWEEN @fromDate AND @toDate)
GROUP BY Weeks.WeekNum

answered Jan 17 '12 at 14:05

UnhandledExcepSean

12,504
2
35
51

This works, and is cleaner, but the other query ran faster on my tests. Thanks for you help – CYMR0 Jan 17 '12 at 15:14

Igor Borisenko · Answer 3 · 2012-01-17T14:31:46.137

As @msmucker0527 wrote get rid of WHERE (sales.transDate BETWEEN @fromDate AND @toDate). You can do it also this way:

SELECT Weeks.WeekNum, SUM(sales.quantity) AS sales
FROM Weeks W  
     LEFT JOIN sales S ON W.WeekNum = DATEPART(week, S.transDate)
            AND S.transDate BETWEEN @fromDate AND @toDate)
GROUP BY W.WeekNum

Also, this WHERE (sales.transDate BETWEEN @fromDate AND @toDate) guarantee the Index Scan of Sales table which can greatly slow down your query.
You'd better include columns WeekFirstDate datetime and WeekLastDate datetime into Weeks table and CREATE NONCLUSTERED INDEX IX_Name ON Sales (TransDate) INCLUDE (quantity). In this case your query can be changed this way:

SELECT Weeks.WeekNum, SUM(sales.quantity) AS sales
FROM Weeks W  
     LEFT JOIN sales S ON S.transDate>=W.WeekFirstDate
                          AND S.transDate<=W.WeekLastDate
                          AND S.transDate BETWEEN @fromDate AND @toDate)
GROUP BY W.WeekNum

Thanks! I'm new to SQL and I have much to learn about indexes and such things. — CYMR0, Jan 17 '12 at 15:17

score 1 · Answer 4 · answered Jan 17 '12 at 14:10

1

Try this:

SELECT Weeks.WeekNum, SUM(sales.quantity) AS sales
FROM Weeks 
LEFT JOIN sales 
       ON Weeks.WeekNum = DATEPART(week, sales.transDate) and
          sales.transDate BETWEEN @fromDate AND @toDate
WHERE Weeks.WeekNum BETWEEN DATEPART(week, @fromDate) AND 
                            DATEPART(week, @toDate)
GROUP BY Weeks.WeekNum

answered Jan 17 '12 at 14:10

@CYMR0: In what way did it not work? What was returned by the query? – Jan 17 '12 at 15:09

Summarise by week, even for empty rows

4 Answers4

Linked