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:

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.

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

Advertisement

Answer