Oracle 10g: Inserting Missing Dates For Table With Others Values

In this question, I was able to insert date in |----------|----------| | DT | FLAG | |----------|----------| | 2015-MAY | E | | 2015-JUN | H | | 2015-OCT |

Solution 1:

What you are looking for here is to perform some data densification, filling in gaps in your data.

Starting with your sparsely populated table, using a partitioned outer join to a dense dimensional table you can achieve your goal:

With Date_Dim(dt) as (
  selectdate'2015-05-01'
       + numtoyminterval(level-1,'month')from dual 
  connect by level <= 14
)
select t1.eid
     , dd.dt
     , nvl(t1.flag, 'V') flagfrom Date_Dim dd
  left join YourData t1 partition by (t1.EID)
    on t1.dt = dd.dt;

In the above code I define the Date_Dim Common Table Expression (CTE) as the dense date dimension, and left outer join it to YourData partitioning the join by the EID column. This alone will ensure that for every eid value there will be at least one row for every DT value in the Date_Dim table. The last bit is to ensure that your flag column returns 'V' instead of NULL, which is simply handled with the NVL function in the queries projection.

Here's a SQL Fiddle showing it in action, and the output generated by the above query in that fiddle:

Results:

|EID|DT|FLAG||-----|----------------------|------||123|2015-05-01T00:00:00Z|E||123|2015-06-01T00:00:00Z|H||123|2015-07-01T00:00:00Z|V||123|2015-08-01T00:00:00Z|V||123|2015-09-01T00:00:00Z|V||123|2015-10-01T00:00:00Z|E||123|2015-11-01T00:00:00Z|V||123|2015-12-01T00:00:00Z|V||123|2016-01-01T00:00:00Z|V||123|2016-02-01T00:00:00Z|E||123|2016-03-01T00:00:00Z|V||123|2016-04-01T00:00:00Z|V||123|2016-05-01T00:00:00Z|V||123|2016-06-01T00:00:00Z|V|

If you want a query suitable for inserting back into your source table of just the missing EID/Date columns you can add a t1.flag is null to the WHERE clause.

Alternately

If you would like a query more like your original you can use a cross product to generate all the rows and minus the original data:

With Date_Dim(dt) as (
  selectdate'2015-05-01'+ numtoyminterval(level-1,'month')
     from dual 
  connectby level <=14
)
select t1.eid, dd.dt, 'V'from Date_Dim dd
 crossjoin YourData t1
minus 
select eid, dt, 'V'from YourData

Solution 2:

If anyone interested, got it work as follow:

FOR employee_rec IN c_employee
        LOOP
            INSERTINTO XE_GRID_OUTPUT
            SELECT  i_employerId, 
                    employee_rec.EMPLOYEEID,
                    to_date(add_months(date'2014-01-01', level -1), 'YYYY-MM-DD') mth,
                    'V'FROM   DUAL 
            CONNECTBY LEVEL <=14
            MINUS
            SELECT EMPLOYERID, EMPLOYEEID, DECLARATIONPERIOD, FLAG
            FROM XE_GRID_OUTPUT
            WHERE EMPLOYEEID=employee_rec.EMPLOYEEID;
        END LOOP;

The cursor selecting the EMPLOYEEID for a given employerId.