CODEWITHSUNDEEP
oracle-databasedateoracle11ginsertsubquery
user6308605
user6308605

Reputation: 731

Insert using subquery by adding dates between two dates based on days [oracle 11g]

This is presentation table:

ID     PRESENTATIONDAY         PRESENTATIONSTART       PRESENTATIONEND        PRESENTATIONSTARTDATE             PRESENTATIONENDDATE 
622         Monday                   12:00:00              02:00:00              01-05-2016                            04-06-2016 
623        Tuesday                   12:00:00              02:00:00              01-05-2016                            04-06-2016 
624        Wednesday                 08:00:00              10:00:00              01-05-2016                            04-06-2016 
625        Thursday                  10:00:00              12:00:00              01-05-2016                            04-06-2016

I would like to insert availabledate in schedule table. This is my current query :

insert into SCHEDULE (studentID,studentName,projectTitle,supervisorID,    
                      supervisorName,examinerID,examinerName,exavailableID,
                     availableday,availablestart,availableend,
                     availabledate) //PROBLEM STARTS HERE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
values (?,?,?,?,?,?,?,?,?,?,?,?));

The value availabledate are retrieved based on the exavailableID . For example, if exavailableID = 2, the availableday = Monday, availablestart= 12pm, availableend = 2pm.

The dates will only be chosen only between PRESENTATIONSTARTDATE to PRESENTATIONENDDATE from presentation table.

In presentation table, it will match PRESENTATIONDAY, PRESENTATIONDATESTART and PRESENTATIONDATEEND with availableday, availablestart and availableend to get a list of all possible dates.

This is the query to get list of all possible dates based on particular days:

select 
  A.PRESENTATIONID,
  A.PRESENTATIONDAY,
  A.PRESENTATIONDATESTART+delta 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 = trim(to_char(A.PRESENTATIONDATESTART+delta, 'Day'))
order by 1,2,3;

This query result is:

622 Monday  02-05-2016 12:00:00
...
622 Monday  30-05-2016 12:00:00
623 Tuesday 03-05-2016 12:00:00
...
623 Tuesday 31-05-2016 12:00:00
624 Wednesday   04-05-2016 12:00:00
...
624 Wednesday   01-06-2016 12:00:00
625 Thursday    05-05-2016 12:00:00
...
625 Thursday    02-06-2016 12:00:00

It will automatically assign dates from the SELECT query to be inserted in schedule table. However, each date can be used only 4 times. Once it reached 4 times, it will proceed to next date. For example, if Monday, '02-05-2016' to '09-05-2016'

How can I corporate these two queries (INSERT and SELECT) to have a result like this:

  StudentName  projectTitle SupervisorID ExaminerID  availableday    availablestart       availableend      availabledate
        abc         Hello     1024       1001            MONDAY         12.00pm                2.00pm         02-05-2016
        def         Hi        1024       1001            MONDAY         12.00pm                2.00pm         02-05-2016
        ghi         Hey       1002       1004            MONDAY         12.00pm                2.00pm         02-05-2016
        xxx         hhh       1020       1011            MONDAY         12.00pm                2.00pm         02-05-2016
        jkl         hhh       1027       1010            MONDAY         12.00pm                2.00pm         09-05-2016 
        try         ttt       1001       1011            MONDAY         12.00pm                2.00pm         09-05-2016
        654         bbb       1007       1012            MONDAY         12.00pm                2.00pm         09-05-2016
        gyg         888       1027       1051            MONDAY         12.00pm                2.00pm         09-05-2016
        yyi         333       1004       1022           TUESDAY         12.00pm                2.00pm         03-05-2016
        fff         111       1027       1041           TUESDAY          ..
        ggg         222       1032       1007           TUESDAY          ..    ..                       ..               ..
        hhh         444       1007       1001           TUESDAY         12.00pm                2.00pm         03-05-2016


         and so on :)

In short, I would like to use the list of dates from presentation table based on the day, start time and end time to insertion query where each date will only used 4 times. Thank you!

Upvotes: 0

Views: 70

Answers (1)

Dahamsta
Dahamsta

Reputation: 127

I am not sure this kind of syntax works with oracle (and have no good way to check), but changing the select part of insert like this may or may not work.

select 
  A.PRESENTATIONID,
  A.PRESENTATIONDAY,
  A.PRESENTATIONDATESTART+delta LIST_DATE
from 
  PRESENTATION A, 
  (
     select level-1 as delta 
     from dual 
     connect by level-1 <= (
       select max(PRESENTATIONDATEEND - PRESENTATIONDATESTART) from PRESENTATION
     )
  ),
  --MIGHT NEED ADDITIONAL LOGIC FOR THE EXAVAILABLEID COMPARISON
  (SELECT count(S.*) as counter FROM SCHEDULE S WHERE S.EXAVAILABLEID=A.ID) C
where A.PRESENTATIONDATESTART+delta <= A.PRESENTATIONDATEEND
and
  a.presentationday = trim(to_char(A.PRESENTATIONDATESTART+delta, 'Day'))
and 
  C.counter<4
order by 1,2,3;

EDIT: Changed the operator. Had >= before. Placed teh WHERE check at the right place. Deleted aliases.

EDIT2: changed the syntax to where the counter select statement is a part of the from clause.

Upvotes: 0

Related Questions