SQL: add missing months from different years

Question

SQL SERVER
[CreatedOn] - DATETIME
I get this table:

Year  Month  Count
2009    7     1 
2009    9     1 
2010    1     2 
2010    3     13

From the request:

SELECT
   YEAR ([CreatedOn]) AS 'Year',
   MONTH ([CreatedOn]) AS 'Month',
   COUNT ([CreatedOn]) AS 'Count'
FROM xxx
GROUP BY YEAR ([CreatedOn]), MONTH ([CreatedOn])

How can I get a table like this (with months missing and Count 0):

Year  Month  Count
2009    7     1
2009    8     0 
2009    9     1 
2009    10    0
2009    11    0
2009    12    0
2010    1     2 
2010    2     0
2010    3     13        

Answer 1

The syntax says you are using MSSQL. Use Recursive CTEto create a table calender, then do Left outer joinwith the tableXXX

DECLARE @maxdate DATE = (SELECT Max([CreatedOn])
   FROM   xxx);

WITH calender
     AS (SELECT Min([CreatedOn]) dates,
         FROM   xxx
         UNION ALL
         SELECT Dateadd(mm, 1, dates)
         FROM   cte
         WHERE  dates < @maxdate)
SELECT Year(dates)         [YEAR],
       Month(dates)        [month],
       Count ([CreatedOn]) AS 'Count'
FROM   calender a
       LEFT OUTER JOIN xxx b
                    ON Year(dates) = Year ([CreatedOn])
                       AND Month(dates) = Month ([CreatedOn])
GROUP  BY Year(dates),
          Month(dates) 

Note: Instead of Recursive CTEcreating a table of physical calendars

Answer 2

To create a calendar, the assembly in the table will be used:

;WITH limits as
(
  SELECT min([CreatedOn]) mi, max([CreatedOn]) ma 
  FROM xxx
), months as(
  SELECT 
    dateadd(mm, number, mi) m
  FROM
    master..spt_values v
  JOIN
    limits l
  ON 
    number between 0 and datediff(mm, l.mi, l.ma) 
  WHERE 
    v.type = 'P'
)
SELECT 
  year(months.m) year, 
  month(months.m) month,
  count(qry.[CreatedOn]) cnt
FROM
  xxx qry 
RIGHT JOIN
  months
ON
  months.m = dateadd(mm, datediff(mm, 0, qry.[CreatedOn]), 0)
GROUP BY
  year(months.m), 
  month(months.m)