Insert subquery date according to day

Question

I would like to insert subquery a date based on it day. Plus, each date can only be used four times. Once it reached fourth times, the fifth value will use another date of same day. In other word, use date of Monday of next week. Example, Monday with 6 JUNE 2016 to Monday with 13 JUNE 2016 (you may check the calendar).

I have a query of getting a list of date based on presentationdatestart and presentationdateend from presentation table:

select   a.presentationid,
         a.presentationday,
         to_char (a.presentationdatestart + delta, 'DD-MM-YYYY', 'NLS_CALENDAR=GREGORIAN') list_date
from     presentation a,
         (select     level - 1 as delta
          from       dual
          connect by level - 1 <= (select max (presentationdateend - presentationdatestart)
                                   from   presentation))
where    a.presentationdatestart + delta <= a.presentationdateend
and      a.presentationday = to_char(a.presentationdatestart + delta, 'fmDay')
order by a.presentationdatestart + delta,
         a.presentationid; --IMPORTANT!!!--

For example,

presentationday    presentationdatestart    presentationdateend
     Monday              01-05-2016             04-06-2016
    Tuesday              01-05-2016             04-06-2016
   Wednesday             01-05-2016             04-06-2016
   Thursday              01-05-2016             04-06-2016

The query result will list all possible dates between 01-05-2016 until 04-06-2016:

Monday  02-05-2016
Tuesday 03-05-2016
Wednesday   04-05-2016
Thursday    05-05-2016
....
Monday      30-05-2016
Tuesday     31-05-2016
Wednesday   01-06-2016
Thursday    02-06-2016 (20 rows)

This is my INSERT query :

insert into CSP600_SCHEDULE (studentID,
                             studentName, 
                             projectTitle,
                             supervisorID,
                             supervisorName,
                             examinerID,
                             examinerName,
                             exavailableID,
                             availableday,
                             availablestart,
                             availableend,
                             availabledate)   
select  '2013816591',
        'mong',
        'abc',
        '1004',
        'Sue',
        '1002',
        'hazlifah',
          2,
        'Monday', //BASED ON THIS DAY
        '12:00:00',
        '2:00:00',
         to_char (a.presentationdatestart + delta, 'DD-MM-YYYY', 'NLS_CALENDAR=GREGORIAN') list_date //FOR AVAILABLEDATE
from     presentation a,
         (select     level - 1 as delta
          from       dual
          connect by level - 1 <= (select max (presentationdateend - presentationdatestart)
                                   from   presentation))
where    a.presentationdatestart + delta <= a.presentationdateend
and      a.presentationday = to_char(a.presentationdatestart + delta, 'fmDay')
order by a.presentationdatestart + delta,
         a.presentationid;

This query successfully added 20 rows because all possible dates were 20 rows. I would like modify the query to be able to insert based on availableDay and each date can only be used four times for each different studentID.

Possible outcome in CSP600_SCHEDULE (I am removing unrelated columns to ease readability):

StudentID   StudentName     availableDay      availableDate
  2013         abc             Monday           01-05-2016
  2014         def             Monday           01-05-2016
  2015         ghi             Monday           01-05-2016
  2016         klm             Monday           01-05-2016
  2010         nop            Tuesday           02-05-2016
  2017         qrs            Tuesday           02-05-2016
  2018         tuv            Tuesday           02-05-2016
  2019         wxy            Tuesday           02-05-2016
  .....
  2039         rrr             Monday           09-05-2016
  ..... 

You may check the calendar :)

Answer 1

I think what you're asking for is to list your students and then batch them up in groups of 4 - each batch is then allocated to a date. Is that right?

In which case something like this should work (I'm using a list of tables as the student names just so I don't need to insert any data into a custom table) :

WITH students AS
(SELECT table_name 
 FROM all_tables
 WHERE rownum < 100
 )
SELECT 
 table_name
,SYSDATE + (CEIL(rownum/4) -1)
FROM
 students
;

I hope that helps you

...okay, following your comments, I think this might be a better solution :

WITH students AS
(SELECT table_name student_name
 FROM all_tables
 WHERE rownum < 100
 )
, dates AS
(SELECT TRUNC(sysdate) appointment_date from dual UNION
 SELECT TRUNC(sysdate+2) from dual UNION
 SELECT TRUNC(sysdate+4) from dual UNION
 SELECT TRUNC(sysdate+6) from dual UNION
 SELECT TRUNC(sysdate+8) from dual UNION
 SELECT TRUNC(sysdate+10) from dual UNION
 SELECT TRUNC(sysdate+12) from dual UNION
 SELECT TRUNC(sysdate+14) from dual 
)
SELECT 
 s.student_name
,d.appointment_date
FROM
 --get a list of students each with a sequential row number, ordered by student name 
 (SELECT
   student_name
  ,ROW_NUMBER() OVER (ORDER BY student_name) rn
 FROM students
 )     s
 --get a list of available dates with a sequential row number, ordered by date
,(SELECT
   appointment_date
  ,ROW_NUMBER() OVER (ORDER BY appointment_date) rn
 FROM dates
 ) d
WHERE 1=1
--allocate the first four students to date rownumber1, next four students to date rownumber 2...
AND CEIL(s.rn/4) = d.rn 
;