|
|
Posted by Notgiven on 01/23/06 15:03
"Pedro Graca" <hexkid@dodgeit.com> wrote in message
news:slrndt2rnp.h04.hexkid@ID-203069.user.individual.net...
> 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>
Thanks. However, if you have a single event scheduled from 8am to 10am on
Mon, Tues, Wed, it would practically place a hold on everything between 8am
Monday and 10a, Wed. This, it would would not allow me to schedule a 2pm
Tuesday event.
[Back to original message]
|