Construction events: Get the condition from the previous inspection

I have a constr_events table:

create table constr_events
   (    
    event_id number(4) not null unique,
    road_id number(4) not null,
    constr_date date not null,
    reset_condition number(9,2) not null
   ) ;
insert into constr_events (event_id, road_id, constr_date, reset_condition) values (1,400,to_date('2016-11-01','YYYY-MM-DD'),1);
insert into constr_events (event_id, road_id, constr_date, reset_condition) values (2,500,to_date('2009-11-01','YYYY-MM-DD'),1);
insert into constr_events (event_id, road_id, constr_date, reset_condition) values (3,500,to_date('2031-11-01','YYYY-MM-DD'),1);
select
    event_id,
    road_id,
    to_char(constr_date,'YYYY-MM-DD') as constr_date_formatted,
    reset_condition
from
    constr_events
order by
    road_id,
    constr_date;

  EVENT_ID    ROAD_ID CONSTR_DATE_FORMATTED  RESET_CONDITION
---------- ---------- ---------------------  ---------------
         1        400 2016-11-01                           1

         2        500 2009-11-01                           1
         3        500 2031-11-01                           1

And a road_insp table:

```
create table road_insp
(
insp_id number(4) not null unique,
road_id number(4) not null,
insp_date date not null,
condition number(9,2) not null
) ;
insert into road_insp (insp_id, road_id, insp_date, condition) values (40,400,to_date('1960-05-01','YYYY-MM-DD'),.8);
insert into road_insp (insp_id, road_id, insp_date, condition) values (41,400,to_date('2009-05-01','YYYY-MM-DD'),.3);
insert into road_insp (insp_id, road_id, insp_date, condition) values (42,400,to_date('2012-05-01','YYYY-MM-DD'),.3);
insert into road_insp (insp_id, road_id, insp_date, condition) values (43,400,to_date('2015-05-01','YYYY-MM-DD'),.2);
insert into road_insp (insp_id, road_id, insp_date, condition) values (45,400,to_date('2019-05-01','YYYY-MM-DD'),.9);

--insert into road_insp (insp_id, road_id, insp_date, condition) values (50,500,to_date('1994-05-01',

To fill in the missing rows you need exactly one row per row in the constr_events table. It's straightforward to get these three columns just by querying that table:


road_id from the the construction event


condition_date from the construction event, but it would be January
1st of that year


type as "CARRYOVER FROM PREVIOUS INSPECTION"

That leaves just this one:


condition from the previous road inspection

A correlated subquery can be a good option when you need to pull exactly one column value from another table. You're on Oracle 12c, so you can take advantage of the expanded ORDER BY syntax. Here's one way to get the results that you want:

SELECT
    road_id,
    TO_DATE('01-01-' || EXTRACT(YEAR FROM constr_date), 'dd-mm-yyyy') condition_date,
    (
        SELECT condition
        FROM road_insp ri
        WHERE ri.road_id = constr_events.road_id
        AND ri.insp_date < constr_events.constr_date
        ORDER BY insp_date DESC FETCH FIRST ROW ONLY
    ) AS condition,
    'CARRYOVER FROM PREVIOUS INSPECTION' AS TYPE
FROM
    constr_events

Depending on the size of your tables you may want an index on the road_id and insp_date columns of the road_insp table. Consider what would happen if the query optimizer implemented the subquery with a nested loop join and there was no index. You would do a full scan of the road_insp table for each row in constr_events. An index as described earlier allows Oracle to jump to the relevant road_id and to find the relevant insp_date without scanning all of the insp_date values. I would expect it to just be a handful of logical reads for each row in constr_events.

If you need to exclude rows with a NULL carryover you can do this by changing the subquery to use CROSS APPLY, LATERAL JOIN, or by wrapping the whole thing in a derived table and filtering on that column outside of the derived table.

StackExchange Database Administrators Q#190735, answer score: 4