Posted by Bill Karwin on 01/21/06 01:50

"Notgiven" <notreallyme@invalid.invalid> wrote in message
news:rwbAf.306$f57.250@bignews7.bellsouth.net...
> SELECT apptID
> FROM appointments
> WHERE (beginningDate = '2006-01-19' OR endingDate = '2006-01-19')
> AND ('08:00:00' BETWEEN beginningTime AND endingTime
> OR '10:00:00' BETWEEN beginningTime AND endingTime)
>
> BUT, what if you have an all-day appointment (8:00am to 5:00pm) and there
> exists an appointment already scheduled from 10:00am to 11:00am. The above
> query would not find it.

Well, you'd need more terms in your condition. See the book "Joe Celko's
SQL for Smarties", specifically section 12.2 on testing for overlapping time
intervals.

> Another question is what if the appointment is more than two days. Say,
> it's from Monday - Wednesday from 8am to 5pm. The above query would not
> successfully catch it if you wanted to schedule an appointment on Tuesday.

Right -- you have complex schedules, so your conditions need to be complex.
Something like this might be closer to what you need:

SELECT . . .
WHERE '2006-01-19' BETWEEN beginningDate AND endingDate
AND ('08:00:00' BETWEEN beginningTime AND endingTime
OR '17:00:00' BETWEEN beginningTime AND endingTime
OR beginningTime BETWEEN '08:00:00' AND '17:00:00'
OR endingTime BETWEN '08:00:00' AND '17:00:00'
)

Regards,
Bill K.

• Next in forum: Re: how do you get a list of all dates between two dates more than a day apart?
• Prev in forum: Re: how do you get a list of all dates between two dates more than a day apart?
• Thread view: Re: how to check for date/time ranges within record (check for schedule conflicts)

[Reply to this message]