Oracle query to fill in the missing data in the same table

Question

I have a table in oracle which has missing data for a given id. I am trying to figure out the sql to fill in the data from start date: 01/01/2019 to end_dt: 10/1/2020. see the input data below. for status key the data can be filled based on its previous status key. see input:

expected output:

Answer 1

You can use a recursive query to generate the dates, then cross join that with the list of distinct ids available in the table. Then, use window functions to bring the missing key values:

with recursive cte (mon) as (
    select date '2019-01-01' mon from dual
    union all select add_months(mon, 1) from cte where mon < date '2020-10-01'
)
select i.id, 
    coalesce(
        t.status_key, 
        lead(t.previous_status_key ignore nulls) over(partition by id order by c.mon)
    ) as status_key,
    coalesce(
        t.status_key,
        lag(t.status_key ignore nulls, 1, -1) over(partition by id order by c.mon)
    ) previous_status_key,
    c.mon
from cte c
cross join (select distinct id from mytable) i
left join mytable t on t.mon = c.mon and t.id = i.id

You did not give a lot of details on how to bring the missing status_keys and previous_status_keys. Here is what the query does:

• status_key is taken from the next non-null previous_status_key

• previous_status_key is taken from the last non-null status_key, with a default of -1

Answer 2

You can generate the dates and then use cross join and some additional logic to get the information you want:

with dates (mon) as (
      select date '2019-01-01' as mon
      from dual
      union all
      select mon + interval '1' month
      from dates
      where mon < date '2021-01-01'
     )
select d.mon, i.id,
       coalesce(t.status_key,
                lag(t.status_key ignore nulls) over (partition by i.id  order by d.mon)
               ) as status_key,
       coalesce(t.previous_status_key,
                lag(t.previous_status_key ignore nulls) over (partition by i.id  order by d.mon)
               ) as previous_status_key
from dates d cross join
     (select distinct id from t) i left join 
     t
     on d.mon = t.mon and i.id = i.id;