title: SQL to display last 12 months, including months without records
body: <p>The company that I work for has a messaging system, and I need to count sent messages, by template, consolidated by month from last 12 months, like the example below. If a template doesn't have msgs in a specified month, it should display zero (in other words, if I have 2 templates, the result will be 24 rows, 12 months by each template):</p>
  
  <pre><code>-----------------------------
  | date    | template | qqty |
  -----------------------------
  | 01/2015 | tpl1     |  100 |
  -----------------------------
  | 01/2015 | tpl2     |  10  |
  -----------------------------
  | 02/2015 | tpl1     |  90  |
  -----------------------------
  | 02/2015 | tpl2     |  0   |
  -----------------------------
  </code></pre>
  
  <p>I'm trying to achieve the results that are asked/aswered into <a href="https://stackoverflow.com/questions/34557199/mysql-count-by-month-including-missing-records">this</a> and <a href="https://stackoverflow.com/questions/31869783/how-to-count-the-number-of-records-in-mysql-by-month-for-the-last-12-months-incl">this</a> posts. We use SQLSERVER instead MySQL.</p>
  
  <p>Basically the system has two tables:</p>
  
  <p><strong>MESSAGE</strong></p>
  
  <pre><code>---------------------------------
  | msg_id | tpl_id | date        |
  ---------------------------------
  | 1      | 1      |  03/01/2015 |
  ---------------------------------
  | 2      | 1      |  15/01/2015 |
  ---------------------------------
  | 3      | 2      |  04/01/2015 |
  ---------------------------------
  | 4      | 1      |  22/02/2015 |
  ---------------------------------
  </code></pre>
  
  <p><strong>TEMPLATE</strong></p>
  
  <pre><code>---------------------
  | tpl_id | tpl_name |
  ---------------------
  | 1      | tpl1     | 
  ---------------------
  | 2      | tpl2     |
  ---------------------
  </code></pre>
  
  <p>I did the following SQL. It works until I enable the template join. If I enable it, the results will show only the months that the system has records...</p>
  
  <pre><code>SELECT
    years.y, months.m, --tpl.tpl_name,
    COUNT(msg.msg_id) AS Total 
  FROM (
    SELECT year(getdate())     AS y UNION ALL
    SELECT year(getdate()) - 1 AS y 
  ) years
  CROSS JOIN (
    SELECT  1 AS m UNION ALL
    SELECT  2 AS m UNION ALL
    SELECT  3 AS m UNION ALL
    SELECT  4 AS m UNION ALL
    SELECT  5 AS m UNION ALL
    SELECT  6 AS m UNION ALL
    SELECT  7 AS m UNION ALL
    SELECT  8 AS m UNION ALL
    SELECT  9 AS m UNION ALL
    SELECT 10 AS m UNION ALL
    SELECT 11 AS m UNION ALL
    SELECT 12 AS m
  ) months
  LEFT JOIN MESSAGE msg ON YEAR(msg.date) = years.y AND MONTH(msg.date) = months.m
  --LEFT JOIN TEMPLATE tpl ON tpl.tpl_id = msg.tpl_id
  WHERE 
      (CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GETDATE())) + '-' + CONVERT(VARCHAR(2), MONTH(GETDATE())) + '-01')) &gt;= DATEADD(month, -12, GETDATE())
      AND (CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GETDATE())) + '-' + CONVERT(VARCHAR(2), MONTH(GETDATE())) + '-01')) &lt;= GETDATE()
  GROUP BY years.y, months.m--, tpl.tpl_name
  ORDER BY years.y, months.m--, tpl.tpl_name
  </code></pre>
  
  <p>My SQL knowledge is basic. I tryed to change the joins to RIGHT, OUTER, etc, but without success.</p>
  
  <p>Please, could you help me point out how do I achieve it?</p>