Group query by date which is between range

Question

I have a table like:

id | date_start | date_end
 1 | 2019.05.01 | 2019.05.06
 2 | 2019.05.05 | 2019.05.05
 3 | 2019.05.05 | 2019.05.08

Date_start and date_end means that entry is active during this period. Is it possible to get days with his count of active entries for intermediate values too? Like:

2019.05.01 | 1
2019.05.02 | 1
2019.05.03 | 1
2019.05.04 | 1
2019.05.05 | 3
2019.05.06 | 2
2019.05.07 | 1
2019.05.08 | 1

You might consider handling the display logic in application code. Failing that, let us know which version you're using because this is considerably simplified in MySQL 8+ — Strawberry, May 17 '19 at 16:29

score 1 · Answer 1 · answered May 17 '19 at 17:13

I think that this will work for you:

          SELECT temp_date, 
             COUNT(*) 
        FROM (
              SELECT ((SELECT MAX(date_start) FROM {dates_table}) - INTERVAL c.number DAY) AS temp_date
                FROM (SELECT singles + tens + hundreds number 
                        FROM ( SELECT 0 singles
                             UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
                             UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
                             UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
                             ) singles 
                        JOIN (SELECT 0 tens
                             UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
                             UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
                             UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
                             ) tens  
                        JOIN (SELECT 0 hundreds
                             UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
                             UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
                             UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
                             ) hundreds
                    ORDER BY number DESC) c  
               WHERE c.number BETWEEN 0 AND (SELECT DATEDIFF(MAX(date_end),MIN(date_start)) FROM {dates_table})
             ) calendar
        JOIN {dates_table} ON temp_date BETWEEN end_date AND start_date
    GROUP BY temp_date

Just replace {dates_table} with the name of your table.

The base of this that generates the series of dates was taken from IvanD's answer on this SO question: How to populate a table with a range of dates?

Group query by date which is between range

1 Answers1