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:

This is not difficult - but I would like clarification on something. When data is missing (at the beginning of 2019), why is it populated as `-1`? The natural way to indicate the absence of data is `null`, not some made-up, otherwise-invalid value. — , Oct 15 '20 at 17:15
Furthermore, I don't see how you would infer the date (`MON`) for the new records. Please describe the logic in more details. — Michal J Figurski, Oct 15 '20 at 17:16
the value -1 is just a made up value for null. Mon column represents the first day of each month between the dates 1/1/2019 and 10/1/2020 — user1751356, Oct 15 '20 at 17:18

score 2 · Answer 1 · answered Oct 15 '20 at 17:17

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

score 2 · Answer 2 · answered Oct 15 '20 at 17:18

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;

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

2 Answers2