Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Question

RE: SQL Question

From: Rudy Zung <Rudy.Zung_at_efi.com>
Date: Mon, 3 May 2004 16:19:42 -0400
Message-ID: <4FBAA533C7C64940A7921F3000736B121211A4@pghexmb01.printcafe.efi.internal> 






Narrowly crafted solution to your question.



Works: appears to work, but YMMV.


Efficient: uh, no.



create table COURSE_SCHEDULE


(ID	    number,



 COURSE	    varchar2(20),


 START_TIME date,


 END_TIME   date)


/


insert into COURSE_SCHEDULE values


(1191, 'EDU*140',=20




to_date('2004-05-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1192, 'EDU*210',=20




to_date('2004-05-01 08:00:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 09:35:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1194, 'EDU*213',=20




to_date('2004-05-01 18:00:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 21:00:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1195, 'EDU*225',=20




to_date('2004-05-01 09:35:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 11:00:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1196, 'EDU*225',=20




to_date('2004-05-01 11:10:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 12:35:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1198, 'EDU*226',=20




to_date('2004-05-01 14:20:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 17:35:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1199, 'EDU*226',=20




to_date('2004-05-01 19:00:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 22:15:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1201, 'EDU*228',=20




to_date('2004-05-01 19:00:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 22:15:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1204, 'EDU*233',=20




to_date('2004-05-01 19:00:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 22:15:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1205, 'EDU*250',=20




to_date('2004-05-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1211, 'ENG*010',=20




to_date('2004-05-01 08:00:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 08:55:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1212, 'ENG*010',=20




to_date('2004-05-01 12:45:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 14:10:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1213, 'ENG*010',=20




to_date('2004-05-01 19:00:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 22:15:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1216, 'ENG*011',=20




to_date('2004-05-01 08:00:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 08:55:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1217, 'ENG*011',=20




to_date('2004-05-01 09:05:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 10:00:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1218, 'ENG*011',=20




to_date('2004-05-01 09:05:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 10:00:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1219, 'ENG*011',=20




to_date('2004-05-01 10:10:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 11:05:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1220, 'ENG*011',=20




to_date('2004-05-01 10:10:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 11:05:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into COURSE_SCHEDULE values


(1221, 'ENG*011',=20




to_date('2004-05-01 11:15:00', 'yyyy-mm-dd hh24:mi:ss'),=20
to_date('2004-05-01 12:10:00', 'yyyy-mm-dd hh24:mi:ss'));






select distinct

       COURSE,
       to_char(START_TIME, 'yyyy-mm-dd hh24:mi:ss') START_TIME,
       to_char(END_TIME  , 'yyyy-mm-dd hh24:mi:ss') END_TIME
   from (select distinct
                LEVEL L,
                cs.*
            from COURSE_SCHEDULE cs
            start with ID =3D (select ID
                                from (select ID
                                         from COURSE_SCHEDULE
                                         where COURSE in (&&COURSES)
                                         order by START_TIME,
                                                  COURSE)
                                where ROWNUM =3D 1)
            connect by COURSE in (&&COURSES) and
                       START_TIME >=3D prior END_TIME)
   where (L, COURSE) in
         (select L,
                 min(COURSE)
             from (select distinct
                          LEVEL L,
                          cs.*
                      from COURSE_SCHEDULE cs
                      start with ID =3D (select ID
                                          from (select ID
                                                   from COURSE_SCHEDULE
                                                   where COURSE in=20
                                                            (&&COURSES)
                                                   order by START_TIME,
                                                            COURSE)
                                           where ROWNUM =3D 1)
                      connect by COURSE in (&&COURSES) and
                                 START_TIME >=3D prior END_TIME)
             group by L)




   order by START_TIME


/



...Rudy


-----Original Message-----


From: oracle-l-bounce_at_freelists.org =


[mailto:oracle-l-bounce_at_freelists.org] On
Behalf Of deen dayal


Sent: Monday, May 03, 2004 3:15 PM


To: oracle-l_at_freelists.org


Subject: SQL Question




Hi,



I need somebody help with a SQL; I have a course_schedule table which =
has

id	number,
course	varchar2(20),
start_time	date,
end_time	date





Here is some data



NUMB COURSE  START_NEW           END_NEW


=3D=3D=3D=3D =3D=3D=3D=3D =3D=3D=3D=3D=3D=3D=3D =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
1    1191 EDU*140 2004-05-01 09:00:00 2004-05-01 12:00:00
2    1192 EDU*210 2004-05-01 08:00:00 2004-05-01 09:35:00
3    1194 EDU*213 2004-05-01 18:00:00 2004-05-01 21:00:00
4    1195 EDU*225 2004-05-01 09:35:00 2004-05-01 11:00:00
5    1196 EDU*225 2004-05-01 11:10:00 2004-05-01 12:35:00
6    1198 EDU*226 2004-05-01 14:20:00 2004-05-01 17:35:00
7    1199 EDU*226 2004-05-01 19:00:00 2004-05-01 22:15:00
8    1201 EDU*228 2004-05-01 19:00:00 2004-05-01 22:15:00
9    1204 EDU*233 2004-05-01 19:00:00 2004-05-01 22:15:00
10   1205 EDU*250 2004-05-01 09:00:00 2004-05-01 12:00:00
11   1211 ENG*010 2004-05-01 08:00:00 2004-05-01 08:55:00
12   1212 ENG*010 2004-05-01 12:45:00 2004-05-01 14:10:00
13   1213 ENG*010 2004-05-01 19:00:00 2004-05-01 22:15:00
14   1216 ENG*011 2004-05-01 08:00:00 2004-05-01 08:55:00
15   1217 ENG*011 2004-05-01 09:05:00 2004-05-01 10:00:00
16   1218 ENG*011 2004-05-01 09:05:00 2004-05-01 10:00:00
17   1219 ENG*011 2004-05-01 10:10:00 2004-05-01 11:05:00


18   1220 ENG*011 2004-05-01 10:10:00 2004-05-01 11:05:00

19   1221 ENG*011 2004-05-01 11:15:00 2004-05-01 12:10:00



what I need is a SQL which can give me non overlapping course schedules=20
for a given set of courses, giving alphabetical priority to courses.



For example for a given set of courses EDU*140, ENG*010, ENG*011


ENG*010  2004-05-01 08:00:00 2004-05-01 08:55:00
EDU*140  2004-05-01 09:00:00 2004-05-01 12:00:00
ENG*010  2004-05-01 12:45:00 2004-05-01 14:10:00
ENG*010  2004-05-01 19:00:00 2004-05-01 22:15:00






I can not come up with any ideas, can some body please help?




Thanks for any ideas/help



Deen




Please see the official ORACLE-L FAQ: http://www.orafaq.com




To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


Received on Mon May 03 2004 - 15:16:52 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US