I am attempting to join two tables based on date ranges.
Table A format is:
x
ID CAT DATE_START DATE_END
1 10 2018-01-01 2020-12-31
2 15 2018-06-01 2018-07-01
Table B format is:
ID YEAR VALUE
1 2017 100
1 2018 110
1 2019 90
1 2020 30
2 2018 200
The resulting table should be merged if for a given ID, any of the days in B.YEAR are included in the date range from A.DATE_START to A.DATE_END, and should look like this:
ID YEAR CAT VALUE
1 2018 10 110
1 2019 10 90
1 2020 10 30
2 2018 15 200
I tried merging using extract(year from DATE_START)
and extract(year from DATE_START)
, but I cannot manage to include the middle year 2019 in the interval, which means ID = 1 is missing its 2019 value.
I also tried merging using to_date(YEAR), 'YYYY')
, but the generated date for YEAR = ‘2018’ is ‘1.9.2018’, which does not fall in the interval for ID = 2. Thanks a lot for help.
Advertisement
Answer
Join the tables like this:
select a.ID, b.YEAR, a.CAT, b.VALUE
from TableA a inner join TableB b
on b.ID = a.ID
and b.year between extract(year from a.DATE_START) and extract(year from a.DATE_END)
See the demo.
Results:
> ID | YEAR | CAT | VALUE
> -: | ---: | --: | ----:
> 1 | 2018 | 10 | 110
> 1 | 2019 | 10 | 90
> 1 | 2020 | 10 | 30
> 2 | 2018 | 15 | 200