Posted by Pedro Graca on 01/21/06 01:17

Notgiven wrote:
> You want to check scheduling conflicts and you have a record like:
>
> appointments(table):
> apptID
> beginningDate
> endingDate
> beginningTime
> endingTime

I might have created the table differently :-)

appointments(table):
apptID
beginningDateTime
endingDateTime

> It's easy enough to check if a time is within that record. Say you want to
> check if 8:00am to 10:00am is available, you would use this:
>
> 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)

SELECT apptID
FROM appointments
WHERE (beginningDateTime BETWEEN 20060119080000 AND 20060119100000)
OR (endingDateTime BETWEEN 20060119080000 AND 20060119100000)
OR (20060119090000 /* could be anything between 8am and 10am */
BETWEEN beginningDateTime AND endingDateTime)

> 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.
>
> 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.
> (I might be able to generate a date range using PHP, don't know if that's
> the best way)
>
> Any ideas? Thank you very much for any help!

Maybe this other structure raises other problems not specified in your
article ...

HTH

--
If you're posting through Google read <http://cfaj.freeshell.org/google>

• 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: Can you _POST variables in php without using a <FORM>
• Thread view: Re: how to check for date/time ranges within record (check for schedule conflicts)

[Reply to this message]