Predefined range of date to fill

Question

I want to count the number of item by date, but sometimes the table doesn't contains rows with some dates, and I have a hole in the result, like :

+------------+--------+
| date       | number |
+------------+--------+
| 2021-03-12 |      1 |
| 2021-03-15 |      1 |
| 2021-03-17 |      1 |
| 2021-03-18 |      1 |
+------------+--------+

I would like this result:

+------------+--------+
| date       | number |
+------------+--------+
| 2021-03-12 |      1 |
| 2021-03-13 |      0 |
| 2021-03-14 |      0 |
| 2021-03-15 |      1 |
| 2021-03-16 |      0 |
| 2021-03-17 |      1 |
| 2021-03-18 |      1 |
+------------+--------+

So, I created a temporary table with WITH, it works:

WITH recursive Date_Ranges AS (
       select DATE(NOW() - INTERVAL 3 MONTH) AS Date
       union
       select Date + interval 1 day
       from Date_Ranges
       where Date < DATE(NOW())
   )
    SELECT *, 0 AS number FROM Date_Ranges

Next, I want to retrieve the statistics, I tried with this query:

WITH recursive Date_Ranges AS (
   select DATE(NOW()- INTERVAL 3 MONTH) AS Date
   union
   select Date + interval 1 day
   from Date_Ranges
   where Date < DATE(NOW())
)
select *, '0' AS value from Date_Ranges
UNION DISTINCT 
SELECT DATE(time) AS date, count(*) AS number
                    FROM my_table
                    WHERE time >= NOW()- INTERVAL 3 MONTH
                    AND time <= DATE(NOW())
                    GROUP BY DATE(ca_time)

But the UNION doesn't work because the value is different so mysql keep the 2 lines:

example:
+------------+--------+
| date       | number |
+------------+--------+
| 2021-03-12 |      0 |
| 2021-03-13 |      0 |
| 2021-03-14 |      0 |
| 2021-03-15 |      0 |
| 2021-03-16 |      0 |
| 2021-03-17 |      0 |
| 2021-03-18 |      0 |
| 2021-03-12 |      2 |
| 2021-03-13 |      6 |
| 2021-03-14 |      7 |
| 2021-03-15 |      8 |
| 2021-03-16 |      9 |
| 2021-03-17 |      5 |
| 2021-03-18 |      0 |
+------------+--------+

How the lines with 0 can be merge with the second union ?

Thanks.

Edit:

I tried with this query, but dates without result are not here:

WITH recursive dates AS (
   SELECT DATE(NOW() - INTERVAL 3 MONTH) AS Date
   UNION ALL
   SELECT Date + interval 1 day
   FROM dates
   WHERE Date < DATE(NOW())
)
SELECT date, COALESCE(COUNT(1), 0) AS number
FROM dates
LEFT JOIN geo
ON dates.date = DATE(geo.ca_time)
WHERE departmentCode = '89'
GROUP BY geo.ca_time
ORDER BY geo.ca_time

result:

+------------+--------+
| date       | number |
+------------+--------+
| 2021-02-21 |      1 |
| 2021-02-22 |     11 |
| 2021-02-27 |      8 |
...

Use `calendar left join datatable` and `COALESCE(number, 0)`. — Akina, May 17 '21 at 13:39
Alternatively, consider handling issues of data display in application code — Strawberry, May 17 '21 at 14:03
Thanks @Akina, I read the the part "Date Series Generation" on https://dev.mysql.com/doc/refman/8.0/en/with.html but in their example it's a SUM, not a COUNT. And if I try with a COUNT, I never get 0, the min is 1... — Surfoo, May 17 '21 at 22:47
Your `WHERE` condition is removing rows which would have `0` values in them as `departmentCode` will be `NULL`. Move that condition into the `LEFT JOIN` instead i.e. `LEFT JOIN geo ON dates.date = DATE(geo.ca_time) AND departmentCode = '89'` — Nick, May 17 '21 at 23:26

Serg · Accepted Answer · 2021-05-18T08:08:12.360

1

UNION ALL version

WITH recursive Date_Ranges AS (
   select DATE(NOW()- INTERVAL 3 MONTH) AS Date
   union
   select Date + interval 1 day
   from Date_Ranges
   where Date < DATE(NOW())
)   
select date, count(*) - 1 AS number
from (
   select date
   from Date_Ranges
   union all
   select DATE(time) 
   from my_table
   where time >= NOW() - INTERVAL 3 MONTH
             AND time <= DATE(NOW())
) t
group by date

db<>fiddle

edited May 18 '21 at 08:08

answered May 17 '21 at 13:54

Serg

22,285
5
21
48

Hello, Thanks for your answer, I tried with my real query but there is a syntax error on the GROUP BY : ``` WITH recursive Date_Ranges AS ( SELECT DATE(NOW() - INTERVAL 3 MONTH) AS Date UNION SELECT Date + interval 1 day FROM Date_Ranges WHERE Date < DATE(NOW()) ) SELECT *, count(*) - 1 AS number FROM ( SELECT date FROM Date_Ranges UNION ALL SELECT DATE(ca_time) AS date FROM geo WHERE DATE(ca_time) >= DATE(NOW()) - INTERVAL 3 MONTH AND departmentCode = '89' AND DATE(ca_time) <= DATE(NOW()) ) GROUP BY date ``` – Surfoo May 17 '21 at 21:45
1

See edited answer , alias was missing. – Serg May 18 '21 at 07:48

eshirvana · Answer 2 · 2021-05-18T03:25:31.087

0

join your cte with your table :

WITH recursive Date_Ranges AS (
   select DATE(NOW()- INTERVAL 3 MONTH) AS Date
   union
   select Date + interval 1 day
   from Date_Ranges
   where Date < DATE(NOW())
)

select dr.date ,coalesce(sum(t.num),0)
from Date_Ranges dr
left join my_table t
 on dr.date = t.datecol
group by dr.date

db<>fiddle here

edited May 18 '21 at 03:25

answered May 17 '21 at 13:40

eshirvana

23,227
3
22
38

Thanks, but the sql result have some holes : ``` +------------+--------+ | date | number | +------------+--------+ | 2021-02-21 | 1 | | 2021-02-22 | 11 | | 2021-02-27 | 8 | ``` – Surfoo May 17 '21 at 21:52
@Surfoo , that works for me perfectly , see *db<>fiddle [here](https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=77c21e8444d96b4eb5f4a3d2327d91b8)* – eshirvana May 18 '21 at 03:25

Predefined range of date to fill

2 Answers2