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

am 20.01.2006 21:18:15 von NotGiven

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

appointments(table):
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

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

am 21.01.2006 00:17:03 von Pedro Graca

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

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

am 21.01.2006 00:17:03 von Pedro Graca

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

am 21.01.2006 00:50:12 von Bill Karwin

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

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

am 21.01.2006 00:50:12 von Bill Karwin

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

am 23.01.2006 14:03:48 von NotGiven

"Pedro Graca" 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

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.

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

am 23.01.2006 14:03:48 von NotGiven

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

am 23.01.2006 14:21:37 von NotGiven

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

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

am 23.01.2006 14:21:37 von NotGiven

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

am 23.01.2006 18:41:47 von Bill Karwin

"Notgiven" wrote in message
news:LH4Bf.2884$5O2.1178@bignews4.bellsouth.net...
>
>> 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?

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.

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

am 23.01.2006 18:41:47 von Bill Karwin