Example tables are below,
x
CREATE TABLE TIMEOFF_INFO (
TIMEOFF_ID INT PRIMARY KEY,
TIMEOFF_BEGIN DATE NOT NULL,
TIMEOFF_END DATE NOT NULL
)
and example rows below,
+------------+---------------+-------------+
| timeoff_id | timeoff_begin | timeoff_end |
+------------+---------------+-------------+
| 1 | 2021-10-01 | 2021-10-02 |
+------------+---------------+-------------+
| 2 | 2021-11-15 | 2021-11-15 |
+------------+---------------+-------------+
| 3 | 2021-12-18 | 2021-12-20 |
+------------+---------------+-------------+
What I want to get is convert table above to below so that I can join using each date in range.
2021-10-01 (id: 1's begin date)
2021-10-02 (id: 1's end date)
2021-11-15 (id: 2's begin and end date)
2021-12-18 (id: 3's begin date)
2021-12-19
2021-12-20 (id: 3's end date)
Is there any way to extend date range in single row to series of Date row?
Advertisement
Answer
WITH RECURSIVE
daterange AS ( SELECT MIN(TIMEOFF_BEGIN) dStart, MAX(TIMEOFF_END) dEnd
FROM TIMEOFF_INFO ),
calendar AS ( SELECT dStart `date`
FROM daterange
UNION ALL
SELECT `date` + INTERVAL 1 DAY
FROM calendar
WHERE `date` < ( SELECT dEnd FROM daterange ) )
SELECT calendar.`date`
FROM calendar
WHERE EXISTS ( SELECT NULL
FROM TIMEOFF_INFO
WHERE calendar.`date` BETWEEN TIMEOFF_INFO.TIMEOFF_BEGIN AND TIMEOFF_INFO.TIMEOFF_END )
MariaDB 10.2+ or MySQL 8+ needed.