recursively creating multiple rows for every date between two dates in one row

I have staff holidays (rdos) stored in a single row, I am very close to recursively expanding them to show a full row for each day of leave. Running this on Postgres Database version 10.10

Data Set:

id |user_id|start_date|finish_date|
---|-------|----------|-----------|
412|     14|2020-10-20| 2020-10-22|
411|    137|2020-10-20| 2020-10-30|
406|     78|2020-07-18| 2020-07-18|
405|    109|2020-07-22| 2020-07-23|
403|     37|2020-06-10| 2020-06-10|

My query currently:

with recursive rdos(id,user_id,start_date,finish_date,dates,depth,path,cycle) AS (
select
staff_rdos.id,
staff_rdos.user_id, 
staff_rdos.start_date,
staff_rdos.finish_date,
staff_rdos.start_date::timestamp,
1,
ARRAY[staff_rdos.id::INT],
false
from staff_rdos
union all
select
staff_rdos.id,
staff_rdos.user_id, 
staff_rdos.start_date,
staff_rdos.finish_date,
rdos.dates + interval '1 day',
rdos.depth + 1,
path || rdos.depth + 1,
staff_rdos.id = ANY(path)
from rdos, staff_rdos
where rdos.dates + interval '1 day' <= staff_rdos.finish_date
and rdos.dates + interval '1 day' >= staff_rdos.start_date
and not cycle
)select * from rdos 
limit 200

I am getting duplicate regressions and unwanted cross matches.

Any ideas? I have seen similar solutions that rely on a unioned table of the months, but i don’t know if that would apply here.

Answer

No need for a recursive query to achieve such task In Postgres: you can just use generate_series() and a lateral join:

select sr.id, sr.user_id, x.vacation_date
from staff_rdoos sr
cross join lateral generate_series(sr.start_date, sr.end_date, '1 day') x(vacation_date)

Advertisement

Answer