SQL to display last 12 months, including months without records

Question

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):

-----------------------------
| date    | template | qqty |
-----------------------------
| 01/2015 | tpl1     |  100 |
-----------------------------
| 01/2015 | tpl2     |  10  |
-----------------------------
| 02/2015 | tpl1     |  90  |
-----------------------------
| 02/2015 | tpl2     |  0   |
-----------------------------

I'm trying to achieve the results that are asked/aswered into this and this posts. We use SQLSERVER instead MySQL.

Basically the system has two tables:

MESSAGE

---------------------------------
| 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 |
---------------------------------

TEMPLATE

---------------------
| tpl_id | tpl_name |
---------------------
| 1      | tpl1     | 
---------------------
| 2      | tpl2     |
---------------------

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...

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')) >= DATEADD(month, -12, GETDATE())
    AND (CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GETDATE())) + '-' + CONVERT(VARCHAR(2), MONTH(GETDATE())) + '-01')) <= GETDATE()
GROUP BY years.y, months.m--, tpl.tpl_name
ORDER BY years.y, months.m--, tpl.tpl_name

My SQL knowledge is basic. I tryed to change the joins to RIGHT, OUTER, etc, but without success.

Please, could you help me point out how do I achieve it?

mind if i ask what `(CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GETDATE())) + '-' + CONVERT(VARCHAR(2), MONTH(GETDATE())) + '-01')) >= DATEADD(month, -12, GETDATE()) AND (CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GETDATE())) + '-' + CONVERT(VARCHAR(2), MONTH(GETDATE())) + '-01')) <= GETDATE()` is for? — JamieD77, Jun 02 '16 at 18:49
@JamieD77 After that you ponted out, it's garbage. I removed it from the query. — Bob Rivers, Jun 02 '16 at 19:17

score 1 · Accepted Answer · answered Jun 02 '16 at 18:42

cross join TEMPLATE first then left join messages.

FROM    (...
        ) years
        CROSS JOIN (...
                   ) months
        CROSS JOIN TEMPLATE tpl
        LEFT JOIN MESSAGE msg ON YEAR(msg.date) = years.y
                                 AND MONTH(msg.date) = months.m AND tpl.tpl_id = msg.tpl_id

score 1 · Answer 2 · answered Jun 02 '16 at 18:43

Conceptually, you need to build the list of items to report on (year x month x template), and then work in (via outer joins) the data for each item in the list. Here, I changed the join on TEMPLATE into a cross join:

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
CROSS JOIN TEMPLATE tpl  --  Assumes there are just the two templates in the table
LEFT JOIN MESSAGE msg
  ON YEAR(msg.date) = years.y
   AND MONTH(msg.date) = months.m
   AND msg.tpl_id = tpl.tpl_id
WHERE 
        (CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GETDATE())) + '-' + CONVERT(VARCHAR(2), MONTH(GETDATE())) + '-01')) >= DATEADD(month, -12, GETDATE())
    AND (CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GETDATE())) + '-' + CONVERT(VARCHAR(2), MONTH(GETDATE())) + '-01')) <= GETDATE()
GROUP BY years.y, months.m--, tpl.tpl_name
ORDER BY years.y, months.m--, tpl.tpl_name

(I'm not able to test the query, but it should be good.)

SQL to display last 12 months, including months without records

2 Answers2