how to check for date/time ranges within record (check for schedule conflicts)

[Notgiven]

You want to check scheduling conflicts and you have a record like:

appointments(ta ble):
apptID
beginningDate
endingDate
beginningTime
endingTime

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)

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!

PS: I am using MySQL 3.23.55 and PHP4.x

[Pedro Graca]

Re: how to check for date/time ranges within record (check for schedule conflicts)

Notgiven wrote:[color=blue]
> You want to check scheduling conflicts and you have a record like:
>
> appointments(ta ble):
> apptID
> beginningDate
> endingDate
> beginningTime
> endingTime[/color]

I might have created the table differently :-)

appointments(ta ble):
apptID
beginningDateTi me
endingDateTime
[color=blue]
> 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)[/color]

SELECT apptID
FROM appointments
WHERE (beginningDateT ime BETWEEN 20060119080000 AND 20060119100000)
OR (endingDateTime BETWEEN 20060119080000 AND 20060119100000)
OR (20060119090000 /* could be anything between 8am and 10am */
BETWEEN beginningDateTi me AND endingDateTime)
[color=blue]
> 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![/color]

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>

[Bill Karwin]

Re: how to check for date/time ranges within record (check for schedule conflicts)

"Notgiven" <notreallyme@in valid.invalid> wrote in message
news:rwbAf.306$ f57.250@bignews 7.bellsouth.net ...[color=blue]
> 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.[/color]

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.
[color=blue]
> 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.[/color]

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.

[Notgiven]

Re: how to check for date/time ranges within record (check for schedule conflicts)

"Pedro Graca" <hexkid@dodgeit .com> wrote in message
news:slrndt2rnp .h04.hexkid@ID-203069.user.ind ividual.net...[color=blue]
> Notgiven wrote:[color=green]
>> You want to check scheduling conflicts and you have a record like:
>>
>> appointments(ta ble):
>> apptID
>> beginningDate
>> endingDate
>> beginningTime
>> endingTime[/color]
>
> I might have created the table differently :-)
>
> appointments(ta ble):
> apptID
> beginningDateTi me
> endingDateTime
>[color=green]
>> 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)[/color]
>
> SELECT apptID
> FROM appointments
> WHERE (beginningDateT ime BETWEEN 20060119080000 AND 20060119100000)
> OR (endingDateTime BETWEEN 20060119080000 AND 20060119100000)
> OR (20060119090000 /* could be anything between 8am and 10am */
> BETWEEN beginningDateTi me AND endingDateTime)
>[color=green]
>> 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![/color]
>
> 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>[/color]

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.

[Notgiven]

Re: how to check for date/time ranges within record (check for schedule conflicts)


"Bill Karwin" <bill@karwin.co m> wrote in message
news:dqrsvf04q6 @enews1.newsguy .com...[color=blue]
> "Notgiven" <notreallyme@in valid.invalid> wrote in message
> news:rwbAf.306$ f57.250@bignews 7.bellsouth.net ...[color=green]
>> 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.[/color]
>
> 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.[/color]

Know where I can see a copy or these pages?
[color=blue][color=green]
>> 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.[/color]
>
> 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.[/color]

That sql code worked perfect - thanks!

[Bill Karwin]

Re: how to check for date/time ranges within record (check for schedule conflicts)

"Notgiven" <notreallyme@in valid.invalid> wrote in message
news:LH4Bf.2884 $5O2.1178@bigne ws4.bellsouth.n et...[color=blue]
>[color=green]
>> 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.[/color]
>
> Know where I can see a copy or these pages?[/color]

In this case, you can also preview some of the relevant pages using
Amazon.com's "search inside the book" feature. The 3rd edition moves the
content I mentioned to chapter 13, pp. 275-286. Looks like pp. 277-281 are
viewable online via:
http://www.amazon.com/gp/product/0123693799/

I recommend purchasing this book, it's full of useful topics.

Regards,
Bill K.