Posted by Notgiven on 01/23/06 15:21

"Bill Karwin" <bill@karwin.com> wrote in message
news:dqrsvf04q6@enews1.newsguy.com...
> "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.

Know where I can see a copy or these pages?

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

That sql code worked perfect - thanks!

[Back to original message]