gariaable gariaable - 20 days ago 7
SQL Question

List months with number of order, AdventureWorks2014

I'm writing a SQL queries using AdventureWorks2014 and I have one, which takes order dates and cuts it into days, months, days of the week and so on. When I use a query which lists all months one by one with number of orders made in this given interval of time, it ignores those month, in which no order was made. I would like to have there a month with 0 orders, but I have no idea how to change this.

Here are my queries:

SELECT DISTINCT
a.SalesOrderID,
CONCAT(DATEPART(yyyy,OrderDate), RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(MONTH, OrderDate)), 2), RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(DAY, OrderDate)), 2)) AS TimeID,
DAY(OrderDate) AS Day,
DATEPART(WEEKDAY, OrderDate) AS 'DayOfWeek',
DATENAME(WEEKDAY, OrderDate) AS 'DayOfWeek Name',
MONTH(OrderDate) AS MONTH,
DATENAME(Month, OrderDate) AS 'Month Name',
DATEPART(QUARTER, OrderDate) as 'Quarter',
YEAR(orderDate) AS YEAR
INTO [DW].[DIMTime]
FROM [AdventureWorks2014].[Sales].[SalesOrderHeader] a;

select MONTH, year, count(SalesOrderID)
from [DW].DIMTime
group by MONTH, Year
order by Year, Month


I hope you could help me! Thanks in advance

Answer

Create a table with all possible combinations of year and month, and use an OUTER JOIN to join this table to your DW.DIMTime table, preserving all months and years from your month-list table.