By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table road_projects (proj_id, road_id, year_, status, from_measure, to_measure) as
select 100, 1, 2022, 'APPROVED', null, 100.1 from dual union all
select 101, 1, 2022, 'APPROVED', 0, 100.1 from dual union all
select 102, 1, 2022, 'APPROVED', 0, 200.6 from dual union all
select 103, 1, 2022, 'APPROVED', 0, 199.3 from dual union all
select 104, 1, 2022, 'APPROVED', 0, 201 from dual union all
select 105, 2, 2023, 'PROPOSED', 0, 50 from dual union all
select 106, 2, 2023, 'PROPOSED', 75, 100 from dual union all
select 107, 3, 2024, 'DEFERRED', 0, 100 from dual union all
select 108, 3, 2025, 'DEFERRED', 0, 110 from dual union all
select 109, 4, 2026, 'PROPOSED', 0, null from dual union all
select 110, 4, 2026, 'DEFERRED', 0, null from dual
11 rows affected
select * from road_projects rp
where exists (
select null
from road_projects rp2
where rp2.proj_id != rp.proj_id
and rp2.road_id = rp.road_id
and rp2.year_ = rp.year_
and rp2.status = rp.status
and abs(coalesce(rp2.from_measure, 0) - coalesce(rp.from_measure, 0)) < 5
and abs(coalesce(rp2.to_measure, 0) - coalesce(rp.to_measure, 0)) < 5
)
order by proj_id
| PROJ_ID | ROAD_ID | YEAR_ | STATUS | FROM_MEASURE | TO_MEASURE |
|---|---|---|---|---|---|
| 100 | 1 | 2022 | APPROVED | null | 100.1 |
| 101 | 1 | 2022 | APPROVED | 0 | 100.1 |
| 102 | 1 | 2022 | APPROVED | 0 | 200.6 |
| 103 | 1 | 2022 | APPROVED | 0 | 199.3 |
| 104 | 1 | 2022 | APPROVED | 0 | 201 |