I have a date_from
and date_to
. I want to get the list of dates in between these two dates, grouped by version.
version | date_from | date_to |
---|---|---|
ver1 | 2020-01-05 | 2020-01-07 |
ver2 | 2021-05-09 | 2021-05-11 |
ver1 | 2022-02-05 | 2022-02-07 |
Result I want
version | date |
---|---|
ver1 | 2020-01-05 |
ver1 | 2020-01-06 |
ver1 | 2020-01-07 |
ver1 | 2022-02-05 |
ver1 | 2022-02-06 |
ver1 | 2022-02-07 |
ver2 | 2021-05-09 |
ver2 | 2021-05-10 |
ver2 | 2021-05-11 |
I tried that recursive query but it didn't work properly :(
WITH RECURSIVE dates(Date) AS
(
SELECT date_from from df as Date
UNION ALL
SELECT date(date, '+1 day') FROM dates WHERE Date < (Select date_to from df)
)
SELECT DATE(Date) FROM dates;
You can use something like following -
with recursive cte (version,date_from,date_to, next_date) as
(select version, date_from, date_to,date_from from gen_dt
union all
select version, date_from, date_to, date(next_date, '+1 day')
from cte c
where next_date < date_to
)select version, next_date from cte order by version, next_date;
Fiddle here.