where子句WHERE (sales.transDate BETWEEN @fromDate AND @toDate)
将删除所有没有销售的星期。您可能需要执行子查询以拉动交易,然后将其加入周表。
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