Filling Month and Year Gaps in Data

Question

I have a table of date-based items, many of which have gaps between months and years. For example, if a post has been created in January, and five in April, I'll have gaps in Feb, March, May, and June. I've been scouring around and found that one thing to do is to use a numbers table, or create a temporary months table, and join off of that, but I still can't seem to get it to work. Here's what I have so far:

CREATE OR REPLACE TABLE temp_months (id INT unsigned PRIMARY KEY);
INSERT INTO temp_months
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12);

SELECT
    COUNT(p.ID) AS COUNT,
    YEAR(p.created_date) as YEAR,
    tm.id as MONTH
FROM
    temp_months tm
LEFT OUTER JOIN
    my_table p
        ON
            MONTH(p.created_date) = tm.id
WHERE
    p.company_id = 123456
GROUP BY
    MONTH, YEAR
ORDER BY
    p.created_date DESC

This gives me the following format, with gaps (almost like I didn't join it to the temp table at all)

+-------+------+-------+
| COUNT | YEAR | MONTH |
+-------+------+-------+
|     1 | 2020 |     5 |
|     3 | 2020 |     2 |
|     1 | 2020 |     1 |
|     9 | 2019 |    10 |
|     2 | 2019 |     8 |
+-------+------+-------+

What I would like it to do, is fill in the gaps with an empty/null/0 COUNT, like:

+-------+------+-------+
| COUNT | YEAR | MONTH |
+-------+------+-------+
|  NULL | 2020 |     6 |
|     1 | 2020 |     5 |
|  NULL | 2020 |     4 |
|  NULL | 2020 |     3 |
|     3 | 2020 |     2 |
|     1 | 2020 |     1 |
|  NULL | 2019 |    12 |
|  NULL | 2019 |    11 |
|     9 | 2019 |    10 |
|  NULL | 2019 |     9 |
|     2 | 2019 |     8 |
|  NULL | 2019 |     7 |
+-------+------+-------+

I'm just not quite sure where I'm messing up.

@Nick I've got two setups, one running `10.3.14` and one running `10.3.23` — Xhynk, Jun 06 '20 at 03:21
You need to move the `p.company_id = 123456` condition into the `JOIN` condition i.e. `LEFT OUTER JOIN my_table p ON MONTH(p.created_date) = tm.id AND p.company_id = 123456` — Nick, Jun 06 '20 at 03:23
Fix that and you'll get close, you might still have an issue with getting the correct years. — Nick, Jun 06 '20 at 03:24
I moved it in there and it's still giving me the same result — Xhynk, Jun 06 '20 at 03:32
You removed the `WHERE` clause, right? Can you post some sample `my_table` data? — Nick, Jun 06 '20 at 03:34
Consider handling issues of data display in application code — Strawberry, Jun 06 '20 at 06:21

zedfoxus · Accepted Answer · 2020-06-06T03:47:32.370

You could try changing your temp_months table to include year like so:

create table temp_months (yr int, mth int, primary key (yr, mth));
insert into temp_months values
(2020, 1), (2020, 2), (2020, 3), (2020, 4), (2020, 5), (2020, 6),
(2019, 7), (2019, 8), (2019, 9), (2019, 10), (2019, 11), (2019, 12);

Assuming your my_table was like so,

create table my_table (created_date date, company_id int, id int);
insert into my_table values
('2020-05-01', 123456, 1),
('2020-02-01', 123456, 1),('2020-02-01', 123456, 1),('2020-02-01', 123456, 1),
('2020-01-01', 123456, 1),
('2019-10-01', 123456, 1),('2019-10-01', 123456, 1),('2019-10-01', 123456, 1),('2019-10-01', 123456, 1),('2019-10-01', 123456, 1),('2019-10-01', 123456, 1),('2019-10-01', 123456, 1),('2019-10-01', 123456, 1),('2019-10-01', 123456, 1),
('2019-08-01', 123456, 1),('2019-08-01', 123456, 1);

You could run this kind of query:

select count(p.id), yr as year, mth as month
from temp_months tm
left join my_table p
  on month(created_date)=tm.mth
  and year(created_date)=tm.yr
group by yr, mth
order by yr desc, mth desc

Result will be

count(p.id) | year | month
----------: | ---: | ----:
          0 | 2020 |     6
          1 | 2020 |     5
          0 | 2020 |     4
          0 | 2020 |     3
          3 | 2020 |     2
          1 | 2020 |     1
          0 | 2019 |    12
          0 | 2019 |    11
          9 | 2019 |    10
          0 | 2019 |     9
          2 | 2019 |     8
          0 | 2019 |     7

If you want NULL to be displayed, you could use:

with result as (
  select count(p.id) as counter, yr as year, mth as month
  from temp_months tm
  left join my_table p
    on month(created_date)=tm.mth
    and year(created_date)=tm.yr
  group by yr, mth
  order by yr desc, mth desc
)
select
  case when counter = 0 then NULL else counter end as counter,
  year, month
from result;

Result will be

counter | year | month
------: | ---: | ----:
   null | 2020 |     6
      1 | 2020 |     5
   null | 2020 |     4
   null | 2020 |     3
      3 | 2020 |     2
      1 | 2020 |     1
   null | 2019 |    12
   null | 2019 |    11
      9 | 2019 |    10
   null | 2019 |     9
      2 | 2019 |     8
   null | 2019 |     7

Example: https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=2ee3594614494d3397a996d7ff815859

To manually but quickly populate temp_months table, I type in a year worth of values like so:

insert into temp_table values
(2019, 1), (2019, 2), (2019, 3), (2019, 4), (2019, 5), (2019, 6),
(2019, 7), (2019, 8), (2019, 9), (2019, 10), (2019, 11), (2019, 12);

Then, I copy that into a text editor, find/replace 2019 to 2020 and execute again...and so on. Within seconds, I'd have years worth of data in temp_table.

The other option is to create a stored procedure to populate it on demand based on example here: How to populate a table with a range of dates?

That works great, thank you! I didn't think about adding in the years as part of the temporary table. My only other question would be, how could I fill in the temporary table dynamically, say for the past 12 or 24 months? — Xhynk, Jun 06 '20 at 03:41
If I were you, I'd manually populate it if it was a few years worth of data. You could use https://stackoverflow.com/questions/10132024/how-to-populate-a-table-with-a-range-of-dates and modify it to dynamically populate dates using a stored procedure. — zedfoxus, Jun 06 '20 at 03:43

Nick · Answer 2 · 2020-06-06T06:59:35.140

Here's a query that will give you results for the last n months, using a recursive CTE to generate the year/month combinations for the last n months and then LEFT JOINing those values to my_table to get the counts for each year/month combination. This query is set up for the last 12 months (11 in the recursive part of the CTE), to change to 24 months you would change that value to 23.

WITH RECURSIVE dates AS (
  SELECT MAX(created_date) AS mdate, CONCAT(LEFT(MAX(created_date), 8), '01') AS cdate
  FROM my_table
  UNION ALL
  SELECT mdate, cdate - INTERVAL 1 MONTH
  FROM dates
  WHERE cdate > mdate - INTERVAL 11 MONTH
)
SELECT COUNT(p.id) AS `count`, YEAR(cdate) AS yr, MONTH(cdate) AS mth
FROM dates d
LEFT JOIN my_table p ON p.created_date BETWEEN d.cdate AND LAST_DAY(d.cdate)
GROUP BY cdate
ORDER BY cdate DESC

Output (for @zedfoxus sample data):

count   yr      mth
1       2020    5
0       2020    4
0       2020    3
3       2020    2
1       2020    1
0       2019    12
0       2019    11
9       2019    10
0       2019    9
2       2019    8
0       2019    7
0       2019    6

Demo on dbfiddle

That query runs from the maximum date in the table, to run from the current date, change the recursive CTE as below:

WITH RECURSIVE dates AS (
  SELECT CONCAT(LEFT(CURDATE(), 8), '01') AS mdate, CONCAT(LEFT(CURDATE(), 8), '01') AS cdate
  UNION ALL
  SELECT mdate, cdate - INTERVAL 1 MONTH
  FROM dates
  WHERE cdate > mdate - INTERVAL 11 MONTH
)
SELECT COUNT(p.id) AS `count`, YEAR(cdate) AS yr, MONTH(cdate) AS mth
FROM dates d
LEFT JOIN my_table p ON p.created_date BETWEEN d.cdate AND LAST_DAY(d.cdate)
GROUP BY cdate
ORDER BY cdate DESC

Demo on dbfiddle

score 0 · Answer 3 · answered Jun 07 '20 at 03:30

0

Assuming you are using MariaDB...

Rather than the messy UNIONs, use seq_0_to_100 and + INTERVAL sea MONTH.

answered Jun 07 '20 at 03:30

Rick James

135,179
13
127
222

Filling Month and Year Gaps in Data

3 Answers3