Construct pattern from a given pattern Oracle-CodePudding

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

CodePudding user response：

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

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

This plsql code:

declare 
    type t_patern_id is table of number index by pls_integer;
    v_patern_id t_patern_id;
    v_max_date date;
    v_end_year date;
    idx number:=0;
    v_date_diff number;
    v_id_date_null number;
    v_count_null number;
    v_null_idx number;
    v_next_null_idx number;
begin
    select min(datum) into v_max_date from patern;
    v_end_year:= trunc((v_max_date   366),'yyyy');
    select  (to_date(v_end_year,'mm/dd/yyyy') - to_date(v_max_date,'mm/dd/yyyy')) 
    into v_date_diff from dual;
    
    select min(patern_id) into v_id_date_null from patern  where datum is null;
    select count(*) into v_count_null from patern where datum is null;
       
    select patern_id bulk collect into v_patern_id from patern;
    idx:=1;
    
    for i in 1..v_date_diff loop

        if v_patern_id(idx)=v_id_date_null then 
            v_null_idx:=idx;
            
            for i in 1..v_count_null loop
                dbms_output.put_line(v_patern_id(idx)||' '||'null');
                if idx=v_patern_id.count
                    then idx:=0;
                end if;
                idx:=idx 1;
            end loop;

            dbms_output.put_line(v_patern_id(idx)||' '||v_max_date);
            v_max_date:= v_max_date 1;
            if idx=v_patern_id.count
                then idx:=0;
            end if;
            idx:=idx 1;
            

            v_next_null_idx:= v_null_idx   v_count_null;
            if v_next_null_idx>v_patern_id.count
                then v_next_null_idx:= v_next_null_idx-v_patern_id.count;
            end if;
            v_id_date_null:= v_patern_id(v_next_null_idx);
        else
            
            dbms_output.put_line(v_patern_id(idx)||' '||v_max_date);
            v_max_date:= v_max_date 1;
            if idx=v_patern_id.count
                then idx:=0;
            end if;
            idx:=idx 1;
        end if;
    end loop;
end;

Gives this result:

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

PL/SQL procedure successfully completed.

The result goes from min(date) from table patern to the end of that year also the code should work for any number of nulls per patern_id. Where the dbms_output.put_line() procedure is u can make an insert into another table. Hope this helps.

CodePudding user response：

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)