sql server - SQL select, pad with chronological missing months

Question

Notice the 2015-02 and 2015-03 months are missing in the output from the following group by SQL. If there is no data for a month I want to show the month and 0. Anyone know how to go about this?

SELECT convert(char(7), MeterReadDate, 121),count(*)
FROM [myTable]
where (MeterReadDate > dateadd(d,-356,getdate()))
group by  convert(char(7), MeterReadDate, 121)
order by  convert(char(7), MeterReadDate, 121)

Sample data:

YYYY-MM COUNT
2014-06 23
2014-07 42
2014-08 80
2014-09 92
2014-10 232
2014-11 88
2014-12 8
2015-01 5
2015-04 2
2015-05 1

Still cannot clear the missing rows, here is where I am with it..

DECLARE @StartDate DATETIME = dateadd(m,-12,getdate()), @EndDate DATETIME = getdate(), @DATE DATETIME

DECLARE @TEMP AS TABLE (MeterReadDate datetime)

SET @DATE = @StartDate

WHILE @DATE <= @EndDate
BEGIN
     INSERT INTO @TEMP VALUES ( @DATE)
    SET @DATE = DATEADD(MONTH,1,@DATE)
END



SELECT convert(char(7), t.MeterReadDate, 121),count(*)

  FROM @TEMP m left join
     [myTable] t
     on convert(char(7), t.MeterReadDate, 121) = convert(char(7), m.MeterReadDate, 121)

  where (t.MeterReadDate > dateadd(m,-12,getdate()))
  group by  convert(char(7), t.MeterReadDate, 121)
  order by  convert(char(7), t.MeterReadDate, 121)

Answer 1

You need a list of dates/months that covers the entire period. Here is one method using a recursive CTE:

with months as (
      select cast(getdate() - 365) as thedate
      union all
      select date_add(1, month, thedate)
      from months
      where thedate <= getdate()
     )
select convert(char(7), m.thedate, 121) as yyyy-mm, count(t.MeterReadDate)
from months m left join
     [myTable] t
     on convert(char(7), MeterReadDate, 121) = convert(char(7), m.thedate, 121)
group by convert(char(7), m.thedate, 121)
order by convert(char(7), m.thedate, 121);