I would like to merge two tables by range of dates.
For each employee in table 2 I will lookup the value in table 1 within the date range.
If I found one value I update, if I found more I will create a new row.
Table_1
Employee start_date end_date value
1 1990-01-01 2019-11-24 false
1 2019-11-25 2019-11-26 true
1 2019-11-27 2020-01-01 false
Table_2
Employee start_date end_date value
1 1990-01-01 2018-12-31 J1
1 2019-01-01 2020-01-01 J2
Merged
Employee start_date end_date value value
1 1990-01-01 2018-12-31 J1 false
1 2019-01-01 2019-11-24 J2 false
1 2019-11-25 2019-11-26 J2 true
1 2019-11-27 2020-01-01 J2 false
I've tried to get all the combinations with an union all and get each time the minimum date but it's not working
select least(t1.startdate, t2.startdate), least(t1.enddate, t2.enddate), t2.value, t1.value from table_2 t2
left join table_1 t1 on t2.id = t1.id and t2.startdate between t1.startdate and t1.enddate
union all
select least(t1.startdate, t2.startdate), least(t1.enddate, t2.enddate), t2.value, t1.value from table_2 t2
left join table_1 t1 on t2.id = t1.id and t1.startdate between t2.startdate and t2.enddate
1990-01-01 2018-12-31 J1 false
1990-01-01 2019-11-24 J2 false
1990-01-01 2018-12-31 J1 false
2019-01-01 2020-01-01 J2 false
2019-01-01 2019-11-26 J2 true