RE: SQL Question
- From: "Rudy Zung" <Rudy.Zung@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 3 May 2004 16:19:42 -0400
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'));
-- this is where you indicate your "a given set of courses"
define COURSES=3D"'EDU*140', 'ENG*010', 'ENG*011'"
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@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of deen dayal
Sent: Monday, May 03, 2004 3:15 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
