Construction events: Get the condition from the previous inspection

Question

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','YYYY-MM-DD'),.6);
--insert into road_insp (insp_id, road_id, insp_date, condition) values (90,500,to_date('2006-05-01','YYYY-MM-DD'),.30);
insert into road_insp (insp_id, road_id, insp_date, condition) values (67,500,to_date('2010-05-01','YYYY-MM-DD'),.9);
insert into road_insp (insp_id, road_id, insp_date, condition) values (68,500,to_date('2014-05-01','YYYY-MM-DD'),.9);
insert into road_insp (insp_id, road_id, insp_date, condition) values (69,500,to_date('2017-05-01','YYYY-MM-DD'),.9);
insert into road_insp (insp_id, road_id, insp_date, condition) values (70,500,to_date('2020-05-01','YYYY-MM-DD'),.8);
insert into road_insp (insp_id, road_id, insp_date, condition) values (71,500,to_date('2023-05-01','YYYY-MM-DD'),.8);
insert into road_insp (insp_id, road_id, insp_date, condition) values (72,500,to_date('2026-05-01','YYYY-MM-DD'),.7);
insert into road_insp (insp_id, road_id, insp_date, condition) values (73,500,to_date('2029-05-01','YYYY-MM-DD'),.6);
insert into road_insp (insp_id, road_id, insp_date, condition) values (74,500,to_date('2032-05-01','YYYY-MM-DD'),.9);
insert into road_insp (insp_id, road_id, insp_date, condition) values (75,500,to_date('2035-05-01','YYYY-MM-DD'),.9);
commit;
select
    insp_id,
    road_id,
    to_char(insp_date,'YYYY-MM-DD') as insp_date_formatted,
    condition as condition
from
    road_insp
order by
    road_id,
    insp_date;

   INSP_ID    ROAD_ID INSP_DATE_FORMATTED  CONDITION
---------- ---------- ------------------- ----------
        40        400 1960-05-01                  .8
        41        400 2009-05-01                  .3
        42        400 2012-05-01                  .3
        43        400 2015-05-01                  .2  --last inspection before construction
        45        400 2019-05-01                  .9

        67        500 2010-05-01                  .9
        68        500 2014-05-01                  .9
        69        500 2017-05-01                  .9
        70        500 2020-05-01                  .8
        71        500 2023-05-01                  .8
        72        500 2026-05-01                  .7
        73        500 2029-05-01                  .6  --last inspection before construction
        74        500 2032-05-01                  .9
        75        500 2035-05-01                  .9

When I union the tables together, they look like this:

select
    road_id,
    to_char(insp_date,'YYYY-MM-DD') as condition_date,
    condition as condition,
    'INSPECTION' as type
from
    road_insp
union all
select
    road_id,
    to_char(constr_date,'YYYY-MM-DD') as condition_date,
    reset_condition,
    'CONSTRUCTION' as type
from
    constr_events
order by
    road_id,
    condition_date

   ROAD_ID CONDITION_DATE  CONDITION TYPE        
---------- -------------- ---------- ------------
       400 1960-05-01             .8 INSPECTION  
       400 2009-05-01             .3 INSPECTION  
       400 2012-05-01             .3 INSPECTION  
       400 2015-05-01             .2 INSPECTION  
       400 2016-11-01              1 CONSTRUCTION
       400 2019-05-01             .9 INSPECTION  

       500 2009-11-01              1 CONSTRUCTION
       500 2010-05-01             .9 INSPECTION  
       500 2014-05-01             .9 INSPECTION  
       500 2017-05-01             .9 INSPECTION  
       500 2020-05-01             .8 INSPECTION  
       500 2023-05-01             .8 INSPECTION  
       500 2026-05-01             .7 INSPECTION  
       500 2029-05-01             .6 INSPECTION  
       500 2031-11-01              1 CONSTRUCTION
       500 2032-05-01             .9 INSPECTION  
       500 2035-05-01             .9 INSPECTION

For each construction event, I want to generate a dummy road inspection that gets the condition from the previous inspection.

The dummy inspection would have these attributes:

road_id from the the construction event
condition_date from the construction event, but it would be January 1st of that year
condition from the previous road inspection
type as "CARRYOVER FROM PREVIOUS INSPECTION"

It would look like this:

   ROAD_ID CONDITION_DATE  CONDITION TYPE        
---------- -------------- ---------- ------------
       400 1960-05-01             .8 INSPECTION  
       400 2009-05-01             .3 INSPECTION  
       400 2012-05-01             .3 INSPECTION  
       400 2015-05-01             .2 INSPECTION  
       400 2016-01-01             .2 CARRYOVER FROM PREVIOUS INSPECTION
       400 2016-11-01              1 CONSTRUCTION
       400 2019-05-01             .9 INSPECTION  

       500 2009-11-01              1 CONSTRUCTION
       500 2010-05-01             .9 INSPECTION  
       500 2014-05-01             .9 INSPECTION  
       500 2017-05-01             .9 INSPECTION  
       500 2020-05-01             .8 INSPECTION  
       500 2023-05-01             .8 INSPECTION  
       500 2026-05-01             .7 INSPECTION  
       500 2029-05-01             .6 INSPECTION  
       500 2031-01-01             .6 CARRYOVER FROM PREVIOUS INSPECTION
       500 2031-11-01              1 CONSTRUCTION
       500 2032-05-01             .9 INSPECTION  
       500 2035-05-01             .9 INSPECTION

How can I create a query that generates the dummy construction events?

Update:

I've come across situations where a road's first record is a constr_event, not an insp_event. I've updated the DDL to reflect this scenario (deleted inspections #50 and #90).

When this is the case, @Joe Obbish's query returns null values for the condition. Preferably, instead of returning a record with a null, the queries would simply not return a record at all.

Is there an elegant way to force the query to do this, or is the only solution to wrap the query in a subselect and use a where clause (where condition is not null)?

Are you able to add indexes to the tables?
– Joe Obbish
Nov 14, 2017 at 1:11 — Joe Obbish, Nov 14, 2017 at 1:11

Joe Obbish · Accepted Answer · 2017-11-19 17:55:31Z

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.

In hindsight, I guess I should have named all the fields just as date, and not have three different names for the date fields (insp_date, constr_date and condition_date). It would have made things simpler for people to interpret. Sorry for the confusion. Is it wise to name fields just as date? Since date is an Oracle keyword, and it wouldn't be obvious which table it belongs to? — User1974, Nov 14, 2017 at 21:38
Thanks for the update about excluding nulls. I hadn't noticed this until now (didn't get an alert from SE). — User1974, Nov 20, 2017 at 20:53

User1974 · Accepted Answer · 2017-11-23 18:18:03Z

Create a CTE that unions the two tables together (road_insp_constr_union).
Use the lag function to isolate the inspection that comes previous to the construction event.
Union the result with the CTE.

with road_insp_constr_union as (
        select
            road_id,
            insp_date as condition_date,
            condition as condition,
            'INSPECTION' as type
        from
            road_insp
        union all
        select
            road_id,
            constr_date as condition_date,
            reset_condition,
            'CONSTRUCTION' as type
        from
            constr_events
)
select
    road_id,
    to_date('01-01-' || extract(year from condition_date), 'dd-mm-yyyy') condition_date,
    prev_condition as condition,
    'CARRYOVER FROM PREVIOUS INSPECTION' as type
from
    (
    select
        road_id,
        condition_date,
        condition,
        type,
        lag ( condition ) over ( partition by road_id order by road_id, condition_date ) prev_condition
    from
        road_insp_constr_union
    )
where 
    type = 'CONSTRUCTION'
    and prev_condition is not null
union all
select
    road_id,
    condition_date,
    condition,
    type
from
    road_insp_constr_union
order by
    road_id,
    condition_date;

   ROAD_ID CONDITION  CONDITION TYPE                              
---------- --------- ---------- ----------------------------------
       400 01-MAY-60         .8 INSPECTION                        
       400 01-MAY-09         .3 INSPECTION                        
       400 01-MAY-12         .3 INSPECTION                        
       400 01-MAY-15         .2 INSPECTION                        
       400 01-JAN-16         .2 CARRYOVER FROM PREVIOUS INSPECTION
       400 01-NOV-16          1 CONSTRUCTION                      
       400 01-MAY-19         .9 INSPECTION                        

       500 01-NOV-09          1 CONSTRUCTION                      
       500 01-MAY-10         .9 INSPECTION                        
       500 01-MAY-14         .9 INSPECTION                        
       500 01-MAY-17         .9 INSPECTION                        
       500 01-MAY-20         .8 INSPECTION                        
       500 01-MAY-23         .8 INSPECTION                        
       500 01-MAY-26         .7 INSPECTION                        
       500 01-MAY-29         .6 INSPECTION                        
       500 01-JAN-31         .6 CARRYOVER FROM PREVIOUS INSPECTION
       500 01-NOV-31          1 CONSTRUCTION                      
       500 01-MAY-32         .9 INSPECTION                        
       500 01-MAY-35         .9 INSPECTION

Stack Exchange Network

Construction events: Get the condition from the previous inspection

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
oracle
select
oracle-12c
date
or ask your own question.

Hot Network Questions

Construction events: Get the condition from the previous inspection

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged oracleselectoracle-12cdate or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
oracle
select
oracle-12c
date
or ask your own question.