SQL Question

From: deen dayal <deen.dayal_at_dol.state.nj.us>
Date: Mon, 03 May 2004 15:14:58 -0400
Message-id: <1083611697.17825.41.camel@oralinux>

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

==== ==== ======= =================== ===================
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 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 - 14:12:17 CDT