Fill in the gaps between rows using the previous row's values
with
  date_range as (select min(insp_date) mind, max(insp_date) maxd from road_insp),
  insp_dates as (select add_months(trunc(mind, 'YYYY'), (level - 1)*12) as insp_date 
   from date_range connect by level &lt;= extract(year from maxd) - extract(year from mind))
select
   ri.insp_id, ri.road_id, 
   nvl(case when extract(year from ri.insp_date) = extract(year from id.insp_date) 
     then ri.insp_date else id.insp_date end, ri.insp_date) as insp_date,
   ri.condition
from
  insp_dates id right join
  (select insp_id, road_id, insp_date, condition as condition,
     lead(insp_date) over (partition by road_id order by insp_date) as next_insp_date
  from road_insp) ri 
  on id.insp_date &gt;= trunc(ri.insp_date, 'YYYY') 
    and id.insp_date &lt; trunc(ri.next_insp_date, 'YYYY')
order by insp_id, road_id, insp_date
;


Generate dates between min and max insp_date.

Find the next insp_date for each inspection to have them in 1 row.

Join the generated dates to the original dataset on date ranges based on year, excluding the upper bound, because that is the next inspection with existing data. Outer join, so the last inspection for a road (where next inspection is null) is included as well.

If there is generated insp_date is in the same year as the original data, use the original data, otherwise use the generated insp_date.

   INSP_ID    ROAD_ID INSP_DATE   CONDITION
---------- ---------- ---------- ----------
         1        100 2004-04-01         .9
         1        100 2005-01-01         .9
         1        100 2006-01-01         .9
         1        100 2007-01-01         .9
         1        100 2008-01-01         .9
         1        100 2009-01-01         .9
         1        100 2010-01-01         .9
         2        100 2011-04-01         .7
         3        100 2012-03-01         .7
         3        100 2013-01-01         .7
         3        100 2014-01-01         .7
         3        100 2015-01-01         .7
         3        100 2016-01-01         .7
         4        100 2017-03-01         .6
         5        100 2018-03-01         .6
         6        200 2010-06-01         .4
         6        200 2011-01-01         .4
         7        200 2012-06-01         .3