SQL*Plus: Release 10.2.0.3.0
Oracle9i Release 220.127.116.11.0 - Production
JServer Release 18.104.22.168.0 - Production
I need to be able to create a schedule using views in sqlplus - one where a customer could pick multiple tours, but I need to make sure that both times and days do not overlap. A customer could have up to 4 tours in a week..
I've created tables but am having a little trouble with getting the correct # of viable tours to turn up in the view(s) - #S are dependent on the # of tours specified by customer. I'm only checking for non overlapping times here so far (and I have a script for each set of possible tours) - I'm just not getting the right #s - and I haven't even added days to this script yet.
Am I missing something obvious? ANY helpful ideas?
CREATE VIEW tour1 (T1,O1,d1,st1,et1,T2,O2,d2,st2,et2,T3,O3,d3,st3,et 3,T4,O4,d4,st4,et4) AS
SELECT A.TOURNAME, A.OPERATOR, A.DAYS, A.START_TIME, A.END_TIME,
B.TOURNAME, B.OPERATOR, B.DAYS, B.START_TIME, B.END_TIME,
c.TOURNAME, c.OPERATOR,c.DAYS, c.START_TIME, c.END_TIME,
d.TOURNAME, d.SECTION, d.DAYS, d.START_TIME, d.END_TIME
FROM TOURS A, TOURS B, TOURS c, TOURS d
WHERE A.TOURNAME='WATER' AND B.TOURNAME='DOWNTOWN' and c.TOURNAME='MUSEUMS'and d.TOURNAME='COUNTRY'
AND B.MSTART NOT BETWEEN A.MSTART AND A.MEND
AND B.MEND NOT BETWEEN A.MSTART AND A.MEND
AND A.MSTART NOT BETWEEN B.MSTART AND B.MEND
AND A.MEND NOT BETWEEN B.MSTART AND B.MEND;
Thanks in advance,
p.s. mstart and mend are conversions to 24 hr time
Your query doesn't check for overlaps between A and C, or between B and C.
Your check for no overlap between A and B is OK as long as the dates are all not null (which presumably they are), but could be more simply expressed as:
AND (B.MSTART > A.MEND OR A.MSTART > B.MEND)
I'm not sure what you mean by "mstart and mend are conversions to 24 hr time". I would hope and expect that MSTART and MEND are DATE columns; if they are strings then you need to TO_DATE them to make these comparisons work.
The outcome of the 'available' tours would look like:
Tourname Operator Day TimeBegin TimeEnd Tourname2 Operator2 Day2 TB2 TE2 and so on up to 4 tours. The a,b,c, & d are checking times not overlapping (mstart and mend are military time conversions). I know I've got to check 6 overlaps(AB,AC,AD,BC,BD,CD but thought it might be feasible to do in in separate scripts and then see what comes up from an intersect.
Am I thinking too much?
Also, I haven't yet added a check for the days of the week - I figured one thing at a time, but maybe I need to do them together....
Here is an example - the customer picks these 4 tours, these are the hours and days assigned to those tours. Working with times only this is a bad schedule - but would be ok once days were included.
WATER Wed 1800-2150
DOWNTOWN Thurs 1800-2150
MUSEUMS Mon and Wed 1330-1525
COUNTRY Tues 1710-2100
Even though this would be an okay schedule choice, it doesn't show up as one until the days of the week are added in...I was thinking of something like:
OR A.DAYS = 'M' AND NOT B.DAYS LIKE '%M%'
OR A.DAYS = 'T' AND NOT B.DAYS LIKE '%T%' .....until all possible choices are taken into consideration
thanks!!! I'll try it tonight when I get home - hopefully I won't fall asleep before I get it to work.....I'll let you know how it works out...and if you think of anything else, I'll ready to listen...
Had to add to this for most of trials to run correctly.
B.MSTART > A.MEND OR B.MEND < A.MSTART
I'm still getting some wrong numbers in other trials (usually more tour schedules than I should come up with (they are offered at different times - the ones I "quoted" you were just examples).
I'm wondering if I need some extra code for timesvor for days (for when a W watertour overlaps with a MW Museum tour) - does A.DAYs != B.DAYs catch this instance as an overlap or does it see M vs MW as different days?