Construct pattern from a given pattern Oracle

Question

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...

Answer 1

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.

Answer 2

Here is my sql solution for the same problem, it is very complicated but i don t know better :)

with 
test as (
        select rownum abc,(minu + level - 1) date_list from 
        (select min(datum) minu from patern) min_datum
        connect by level <= 
        trunc((minu+365),'yyyy')+(((trunc((minu+365),'yyyy')-minu)/30)*9)- minu ---> Reduce this number if date goes beyond 31.12.
        ),
test2 as (
        select rownum abc,patern_id from patern
        cross join 
        (select rownum n from dual
         connect by level<=
         (select (trunc((min(datum)+365),'yyyy')- min(datum)) from patern))
        ),
test3 as (
          select ((null_row + (level*9))-10)  first_null_row, (null_row + (level*9))-9 second_null_row from (
          select max(abc) null_row from 
          (select rownum abc, datum from patern) 
          where datum is null)
          connect by level <= 
          (select (trunc((min(datum)+365),'yyyy')- min(datum)) from patern)
          ),
test4 as(
        select rownum abc, a.date_list, b.patern_id
        from test a,test2 b
        where a.abc=b.abc
        ),
test5 as(
        select 
        rownum abc,
        case 
               when a.abc in (select first_null_row from test3) or
                    a.abc in (select second_null_row from test3) then null
               else a.date_list
               end datum,
        patern_id
        from test4 a
        ),
test6 as(
        select rownum abc, patern_id from test5 where datum is not null
        )
        select b.patern_id, a.date_list
        from test4 a,test6 b
        where
        a.abc=b.abc

Result:

PATERN_ID | DATE_LIST
---------------------
3   11.10.2022
4   12.10.2022
7   13.10.2022
10  14.10.2022
11  15.10.2022
3   16.10.2022
4   17.10.2022
5   18.10.2022
6   19.10.2022
11  20.10.2022
3   21.10.2022
4   22.10.2022
5   23.10.2022
6   24.10.2022
7   25.10.2022
10  26.10.2022
4   27.10.2022
5   28.10.2022
6   29.10.2022
7   30.10.2022
10  31.10.2022
11  01.11.2022
3   02.11.2022
6   03.11.2022
7   04.11.2022
10  05.11.2022
11  06.11.2022
3   07.11.2022
4   08.11.2022
5   09.11.2022
10  10.11.2022
11  11.11.2022
3   12.11.2022
4   13.11.2022
5   14.11.2022
6   15.11.2022
7   16.11.2022
3   17.11.2022
4   18.11.2022
5   19.11.2022
6   20.11.2022
7   21.11.2022
10  22.11.2022
11  23.11.2022
5   24.11.2022
6   25.11.2022
7   26.11.2022
10  27.11.2022
11  28.11.2022
3   29.11.2022
4   30.11.2022
7   01.12.2022
10  02.12.2022
11  03.12.2022
3   04.12.2022
4   05.12.2022
5   06.12.2022
6   07.12.2022
11  08.12.2022
3   09.12.2022
4   10.12.2022
5   11.12.2022
6   12.12.2022
7   13.12.2022
10  14.12.2022
4   15.12.2022
5   16.12.2022
6   17.12.2022
7   18.12.2022
10  19.12.2022
11  20.12.2022
3   21.12.2022
6   22.12.2022
7   23.12.2022
10  24.12.2022
11  25.12.2022
3   26.12.2022
4   27.12.2022
5   28.12.2022
10  29.12.2022
11  30.12.2022
3   31.12.2022
        

PS. it is not flexible like plsql code I posted, it won t work for more then 2 nulls per patern_id and sometimes it will go little bit beyond 31.12. of the pattern year (I marked the line of code that regulates date if it goes beyond 31.12. just reduce the number little bit)