Construct pattern from a given pattern Oracle

I want to construct the series from a given pattern stored in a view.Generate the pattern for next 1 year. I tried to userow_number and connect by and Lead ,but was not able to construct. The pattern id can be any random number and not in sequence. Every cycle skip the next two ids.

In other words,from the pattern coming in the view,I have to see which two pattern id's are missing or do not have date and then in next iteration, those pattern id's will have date and the next two in sequence will not have.And so on.. I do not need to show the ones with NULL dates,that's perfectly fine too. I just put to make it understandable.

I am currently using Oracle 12.1

Expected output

and so on...

Solution

For this table (table name is "patern"):

PATERN_ID DATUM
--------------------
3   
4         10/11/2022
5         10/12/2022
6         10/13/2022
7         10/14/2022
10        10/15/2022
11

This plsql code:

declare 
idx number:=1;
v_min_date date;
v_end_year date;
v_date_diff number;
type t_index is table of number index by PLS_INTEGER;
type t_patern_row is table of patern%rowtype index by PLS_INTEGER;
index_null t_index;
v_patern_row t_patern_row;
num_of_repeat number;
begin
    select min(datum) into v_min_date  from patern;
    v_end_year:= trunc((v_min_date + 366),'yyyy');
    select  (to_date(v_end_year,'mm/dd/yyyy') - to_date(v_min_date,'mm/dd/yyyy')) 
    into v_date_diff from dual;
    
    select * bulk collect into v_patern_row from patern order by patern_id;
    num_of_repeat:=ceil(v_date_diff/(v_patern_row.count-2));

    for i in 1..num_of_repeat loop
        for j in v_patern_row.first..v_patern_row.last loop
            if v_patern_row(j).datum is null then
                dbms_output.put_line(v_patern_row(j).patern_id||' '||v_patern_row(j).datum);
            else 
                dbms_output.put_line(v_patern_row(j).patern_id||' '||v_min_date);
                v_min_date:=v_min_date +1;
            end if;
        end loop;
        for r in v_patern_row.first..v_patern_row.last loop
            v_patern_row(r).datum:=v_patern_row(r).datum+4;
            if v_patern_row(r).datum is null then
                index_null(idx):=r;
                idx:=idx+1;
                v_patern_row(r).datum:=to_date('11.11.1111','dd.mm.yyyy');
            end if;
        end loop;
        if index_null(1)=v_patern_row.count then
                index_null(1):=v_patern_row.first+1;
            else
                index_null(1):=index_null(1)+2;
                if index_null(1)>v_patern_row.count then
                       index_null(1):= index_null(1)-v_patern_row.count;
                end if;
        end if;
        if index_null(2)=v_patern_row.count then
                index_null(2):=v_patern_row.first+1;
            else
                index_null(2):=index_null(2)+2;
                if index_null(2)>v_patern_row.count then
                       index_null(2):= index_null(2)-v_patern_row.count;
                end if;
        end if;
        v_patern_row(index_null(1)).datum:=null;
        v_patern_row(index_null(2)).datum:=null;
    end loop;
end;

Gives this result:

3 
4 10/11/2022
5 10/12/2022
6 10/13/2022
7 10/14/2022
10 10/15/2022
11 
3 10/16/2022
4 
5 
6 10/17/2022
7 10/18/2022
10 10/19/2022
11 10/20/2022
3 10/21/2022
4 10/22/2022
5 10/23/2022
6 
7 
10 10/24/2022
11 10/25/2022
3 10/26/2022
4 10/27/2022
5 10/28/2022
6 10/29/2022
7 10/30/2022
10 
11 
3 
4 
5 10/31/2022
6 11/01/2022
7 11/02/2022
10 11/03/2022
11 11/04/2022
3 11/05/2022
4 11/06/2022
5 
6 
7 11/07/2022
10 11/08/2022
11 11/09/2022
3 11/10/2022
4 11/11/2022
5 11/12/2022
6 11/13/2022
7 
10 
11 11/14/2022
3 
4 11/15/2022
5 11/16/2022
6 11/17/2022
7 11/18/2022
10 11/19/2022
11 
3 11/20/2022
4 
5 
6 11/21/2022
7 11/22/2022
10 11/23/2022
11 11/24/2022
3 11/25/2022
4 11/26/2022
5 11/27/2022
6 
7 
10 11/28/2022
11 11/29/2022
3 11/30/2022
4 12/01/2022
5 12/02/2022
6 12/03/2022
7 12/04/2022
10 
11 
3 
4 
5 12/05/2022
6 12/06/2022
7 12/07/2022
10 12/08/2022
11 12/09/2022
3 12/10/2022
4 12/11/2022
5 
6 
7 12/12/2022
10 12/13/2022
11 12/14/2022
3 12/15/2022
4 12/16/2022
5 12/17/2022
6 12/18/2022
7 
10 
11 12/19/2022
3 
4 12/20/2022
5 12/21/2022
6 12/22/2022
7 12/23/2022
10 12/24/2022
11 
3 12/25/2022
4 
5 
6 12/26/2022
7 12/27/2022
10 12/28/2022
11 12/29/2022
3 12/30/2022
4 12/31/2022
5 01/01/2023
6 
7 
10 01/02/2023
11 01/03/2023


PL/SQL procedure successfully completed.

And it still works for this kind of pattern:

PATERN_ID DATUM
--------------------
3         10/12/2022
4   
5   
6         10/13/2022
7         10/14/2022
10        10/15/2022
11        10/16/2022

Here is the result:

3 10/12/2022
4 
5 
6 10/13/2022
7 10/14/2022
10 10/15/2022
11 10/16/2022
3 10/17/2022
4 10/18/2022
5 10/19/2022
6 
7 
10 10/20/2022
11 10/21/2022
3 10/22/2022
4 10/23/2022
5 10/24/2022
6 10/25/2022
7 10/26/2022
10 
11 
3 
4 
5 10/27/2022
6 10/28/2022
7 10/29/2022
10 10/30/2022
11 10/31/2022
3 11/01/2022
4 11/02/2022
5 
6 
7 11/03/2022
10 11/04/2022
11 11/05/2022
3 11/06/2022
4 11/07/2022
5 11/08/2022
6 11/09/2022
7 
10 
11 11/10/2022
3 
4 11/11/2022
5 11/12/2022
6 11/13/2022
7 11/14/2022
10 11/15/2022
11 
3 11/16/2022
4 
5 
6 11/17/2022
7 11/18/2022
10 11/19/2022
11 11/20/2022
3 11/21/2022
4 11/22/2022
5 11/23/2022
6 
7 
10 11/24/2022
11 11/25/2022
3 11/26/2022
4 11/27/2022
5 11/28/2022
6 11/29/2022
7 11/30/2022
10 
11 
3 
4 
5 12/01/2022
6 12/02/2022
7 12/03/2022
10 12/04/2022
11 12/05/2022
3 12/06/2022
4 12/07/2022
5 
6 
7 12/08/2022
10 12/09/2022
11 12/10/2022
3 12/11/2022
4 12/12/2022
5 12/13/2022
6 12/14/2022
7 
10 
11 12/15/2022
3 
4 12/16/2022
5 12/17/2022
6 12/18/2022
7 12/19/2022
10 12/20/2022
11 
3 12/21/2022
4 
5 
6 12/22/2022
7 12/23/2022
10 12/24/2022
11 12/25/2022
3 12/26/2022
4 12/27/2022
5 12/28/2022
6 
7 
10 12/29/2022
11 12/30/2022
3 12/31/2022
4 01/01/2023
5 01/02/2023
6 01/03/2023
7 01/04/2023
10 
11

The result goes from min(date) from table patern to the end of that year.

The code works only for 2 nulls in pattern.

Where the dbms_output.put_line() procedure is u can make an insert into another table.