Skip to content
Advertisement

Matching based on whether year is included (at all) in date range

I am attempting to join two tables based on date ranges.

Table A format is:

Table B format is:

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:

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:

See the demo.
Results:

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement