Counting records by year and month including zero counts

Question

I am using an SQL Server Compact Edition server and I want to count the number of comments per month that correspond to a certain tutorial within a range of dates and include months which have a count of zero. I know I need to join a "calendar" table to my table to account for the missing months, but I need help with correct implementation of this.

I have a table of all the comments from different tutorials. This table is called Comments and the columns I need are [Tutorial] (nvarchar) and [DateAdded] (DateTime).

Tutorial |  DateAdded  
---------+-------------
sample   |  2013-09-02  
sample   |  2013-09-04
sample   |  2013-09-12
sample   |  2013-09-12
example  |  2013-09-15
sample   |  2013-09-16
sample   |  2013-09-21
sample   |  2013-09-30
sample   |  2013-10-01   
sample   |  2013-11-11
sample   |  2013-11-11
example  |  2013-11-14
sample   |  2013-11-15
sample   |  2013-11-19
sample   |  2013-11-21
sample   |  2013-11-25
sample   |  2014-02-04
sample   |  2014-02-06  

And I have a Calendar table which has a year and month column like so:

Year | Month
-----+------
2000 | 01
2000 | 02
.    | .
.    | .
.    | .
2099 | 12

If I were looking for the monthly count of the 'sample' comments from the past year (as of Feb. 14th, 2014), then the ideal output would be:

Tutorial | Year | Month | Count
---------+------+-------+------
 sample  | 2013 |  09   |  7
 sample  | 2013 |  10   |  1
 sample  | 2013 |  11   |  6
 sample  | 2013 |  12   |  0
 sample  | 2014 |  01   |  0
 sample  | 2014 |  02   |  2

I was able to figure out how to do the following query, but I need the months that do not have comments to return 0 as well.

SELECT 
     Tutorial, 
     datepart(year, DateAdded) AS Year, 
     datepart(month, DateAdded) AS Month, 
     COUNT(*) AS Count From Comments 
WHERE 
     DateAdded > DATEADD(year,-1,GETDATE())  
       AND 
     Tutorial='sample' 
GROUP BY 
     Tutorial, 
     datepart(year, DateAdded), 
     datepart(month, DateAdded)

Output using sample data from above.

Tutorial | Year | Month | Count
---------+------+-------+------
 sample  | 2013 |  09   |  7
 sample  | 2013 |  10   |  1
 sample  | 2013 |  11   |  6
 sample  | 2014 |  02   |  2

I know I need to join the tables, but I can't seem to figure out which join to use or how to implement it correctly. Please keep in mind that this is for SQL Server CE, so not all commands from SQL Server can be used.

Thanks so much in advance!

Answer 1

If you have a Calendar table with Month and Year you should try something like

SELECT t2.Tutorial, t1.[Month], t1.[Year], COALESCE(t2.Number, 0) AS Result
  FROM Calendar AS t1 LEFT JOIN (
    SELECT 
      Tutorial, 
      CONVERT(NCHAR(6), DateAdded, 112) AS tutDate,
      COUNT(*) AS Count From Comments 
    WHERE 
      DateAdded > DATEADD(year,-1,GETDATE())  
      AND 
      Tutorial='sample' 
    GROUP BY 
      Tutorial, 
      CONVERT(NCHAR(6), [Order Date], 112)
  ) AS t2
  ON (t1.[Year] + t1.[Month]) = t2.tutDate
  ORDER BY t1.[Year] + t1.[Month]