T SQL Datepart and Count – Show also weeks with zero count
I have following SQL statement:
declare @dateFrom datetime = '2015-01-01'; declare @dateTo datetime = '2015-12-31'; select DATEPART(WEEK, OrderDate) Week, Count(*) Number from table where OrderDate between @dateFrom and @dateTo group by DATEPART(WEEK, OrderDate) order by Week
It returns the number of orders per week, but if there were no orders at all this respective week is omitted.
How can I change the statement so it will also include weeks with 0 orders?
One Solution collect form web for “T SQL Datepart and Count – Show also weeks with zero count”
Gofr1 was on the right track but there are issues with the query.
1 – You do not want to use the datediff() of the begin and end as the stopping condition. It works for a whole year but will not work for partial ranges.
2 – I would add year to the key since that will allow you to handle cross year cases.
3 – You need to roll up the sales before using the Year Week Common Table Expression. Otherwise you just toss out the nulls again (order dates) with the WHERE clause.
Remember, logically the join is applied then the where clause.
The code below uses the Adventure Works 2012 DW database and obtains the correct answer.
- Uses a tally table for some numbers.
- Generates weekly dates and calculates year/week key for given range.
- Rolls up sales from the fact table for given range.
- Left joins the keys to the sales and turns null totals to zero.
-- Declare start and end date DECLARE @dte_From datetime = '2005-07-01'; DECLARE @dte_To datetime = '2007-12-31'; -- About 200K numbers WITH cte_Tally (n) as ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_views a CROSS JOIN sys.all_views b ), -- Create year/week key cte_YearWeekKey (MyKey) as ( SELECT year(dateadd(week, t.n, @dte_from)) * 1000 + datepart(week, dateadd(week, t.n, @dte_from)) as MyKey FROM cte_Tally as t WHERE dateadd(week, t.n, @dte_from) < @dte_To ), -- Must roll up here cte_Sales (MyKey, MyTotal) as ( SELECT YEAR(F.OrderDate) * 1000 + DATEPART(WEEK, F.OrderDate) as MyKey, COUNT(*) as MyTotal FROM [AdventureWorksDW2012].[dbo].[FactResellerSales] F WHERE F.OrderDate between @dte_From and @dte_To GROUP BY YEAR(F.OrderDate) * 1000 + DATEPART(WEEK, F.OrderDate) ) -- Join the results SELECT K.MyKey, ISNULL(S.MyTotal, 0) as Total FROM cte_YearWeekKey as K LEFT JOIN cte_Sales as S ON k.MyKey = S.MyKey