Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2008
    Posts
    7

    Unanswered: non overlapping schedule‏

    SQL*Plus: Release 10.2.0.3.0
    Oracle9i Release 9.2.0.4.0 - Production
    JServer Release 9.2.0.4.0 - Production

    Hi,
    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Nov 2008
    Posts
    7

    non overlapping schedules

    Tony,
    Thanks for answering so quickly

    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....

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Can probably simplify the checks further - from
    Code:
    AND (B.MSTART > A.MEND OR A.MSTART > B.MEND)
    to
    Code:
    AND B.MSTART > A.MEND
    This way, you don't get logical duplicate results where A and B are just swapped around. In full:
    Code:
    AND B.MSTART > A.MEND
    AND C.MSTART > B.MEND
    AND D.MSTART > C.MEND
    I don't see the point in separate scripts really.
    I don't know enough about your data and requirements to comment on how days of the week should be incorporated.

  5. #5
    Join Date
    Nov 2008
    Posts
    7
    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

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What does "Mon and Wed" mean: that this choice involves a visit on both days, or that it is available on both days and customer chooses one?

  7. #7
    Join Date
    Nov 2008
    Posts
    7

    non overlapping schedule

    means available on both days

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In that case I would suggest it is normalised by introducing another table:
    Code:
    create table tour_times
    ( tourname references tours
    , day ...
    , mstart ...
    , mend ...
    );
    
    insert into tour_times (tourname, day, mstart, mend)
    values ('MUSEUMS','MON',1330,1525);
    insert into tour_times (tourname, day, mstart, mend)
    values ('MUSEUMS','WED',1330,1525);
    Then query for non-overlapping tour options where the tour names are all different (unless you want to offer the chance to visit the same museums twice!)

    Your WHERE clause will be something like:
    Code:
    AND (A.DAY != B.DAY OR B.MSTART > A.MEND)
    AND (B.DAY != C.DAY OR C.MSTART > B.MEND)
    ... /* all 6 combinations */
    AND A.TOURNAME != B.TOURNAME
    AND .../* all 6 combinations */
    Note that the new model allows you to run the MUSEUMS tour on any number of days, at different or same times, or even to run it twice on a single day - much more flexible!

  9. #9
    Join Date
    Nov 2008
    Posts
    7

    non overlapping

    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...

    (How do you answer so many of us anyway?)

  10. #10
    Join Date
    Nov 2008
    Posts
    7

    non overlapping schedule

    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?

  11. #11
    Join Date
    Nov 2008
    Posts
    7
    ps that's assuming the wed museum time is the same as the wed water time

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •