List months with number of order, AdventureWorks2014

Question

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 1

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.