Creating calender rotation in plsql for x number of people...

Below is an example to create a rotation for 4 people in 3 step process. Make changes as needed.

1) 

create table test (START_DATE DATE,WEEK_DAY varchar2(20),W_D_N varchar2(10) default null,WEEK_NUMBER number,NAME varchar2(20) default null);

create table test1 ( ID number, NAME varchar2(10) default null);

insert into test1 values (1,'jack');

insert into test1 values (2,'mike');

insert into test1 values (3,'jim');

insert into test1 values (4,'bob');

commit;

2) ---load calender now with appropriate dates and validate randomly:

insert into test (START_DATE,WEEK_DAY,W_D_N,WEEK_NUMBER,NAME)

       SELECT (to_date('2018-08-16','YYYY-MM-DD') +( level -1)) start_date,to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')Week_day,

     ( CASE 

    WHEN  to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')  like 'MON%'   THEN   7      

     WHEN to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')  like 'TUES%'  THEN   1

     WHEN to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')  like 'WED%'   THEN   2

     WHEN  to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY') like 'THUR%'  THEN   3

     WHEN to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')  like 'FRID%'  THEN   4 

     WHEN  to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY') like 'SAT%'   THEN   5

     WHEN to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')  like 'SUN%'   THEN   6

     ELSE

          null

  END ) W_D_N,

        to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1), 'WW') week_number,null

  FROM DUAL CONNECT BY (to_date('2018-08-16','YYYY-MM-DD') +( level -1)) <= to_date('2018-12-31','YYYY-MM-DD');

--------------------------------------------------

3) ---- have a rotation set from the above... change the value "x < 4" based on the rotation..

set serveroutput on

declare 

TYPE at_most_twelve_t IS VARRAY (100) OF VARCHAR2 (100);

l_months   at_most_twelve_t;

type rotat_name is VARRAY (100) OF VARCHAR2 (100);

rotat_cur rotat_name;

--temp varchar2(10);

x NUMBER := 0;

y NUMBER := 0;

z NUMBER := 0;

p NUMBER := 0;

Begin

SELECT distinct week_number  BULK COLLECT INTO l_months FROM test where name is null order by week_number asc;

  select name BULK COLLECT into rotat_cur from test1 order by id asc; 

  for i in 1 .. l_months.count

  LOOP

      -- DBMS_OUTPUT.PUT_LINE('Outer Loop counter is ' || l_months(i));

       ---- set the x value for if loop based on number of people in rotation.

          if  x < 4 then

          x := x + 1;

          else 

           x := 1;

          end if;

             

         -- DBMS_OUTPUT.PUT_LINE('Outer Loop counter is ' || x);

          for k in 1 .. rotat_cur.count 

             loop

                  if    k = x then  

             for j in (select w_d_n from test where week_number=l_months(i))

                 loop 

                 update test set Name=rotat_cur(k) where week_number=l_months(i);

                 commit;

                 end loop;

                  else

                    continue;

                    end if;

            end loop;

  END LOOP;

END;

commit;

===========================================

Hope this helps....