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

[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 <http://cfaj.freeshell.org/google>

[Bill Karwin]

"Notgiven" <no*********@invalid.invalid> wrote in message
news:rw***************@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.

[Notgiven]

"Pedro Graca" <he****@dodgeit.com> wrote in message
news:sl*******************@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.

[Notgiven]

"Bill Karwin" <bi**@karwin.com> wrote in message
news:dq********@enews1.newsguy.com...

"Notgiven" <no*********@invalid.invalid> wrote in message
news:rw***************@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!

[Bill Karwin]

"Notgiven" <no*********@invalid.invalid> wrote in message
news:LH*****************@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.