Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have a table [T] with columns id,start_date, end_date. I want to get id, date, where id is split into multiple dates using start date and end date.
For example:
Input:
| id | start_date | end_date |
|---|---|---|
| 1 | 2022-04-03 | 2022-04-07 |
| 2 | 2022-04-11 | 2022-04-11 |
Expected Output
| id | date |
|---|---|
| 1 | 2022-04-03 |
| 1 | 2022-04-04 |
| 1 | 2022-04-05 |
| 1 | 2022-04-06 |
| 1 | 2022-04-07 |
| 2 | 2022-04-11 |
Build a dates table with all possible dates.
INSERT INTO OutputTable (t_id, date)
SELECT T.id, d.date
FROM T
JOIN dates AS d
WHERE d.date BETWEEN T.start_date AND T.end_date;
(By convention id is assumed to be the PRIMARY KEY, hence unique, for a table. So, I changed to t_id for your output.)
