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 5 9392
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>
"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.
"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.
"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!
"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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: vishal |
last post by:
i want to store the date and time when the user registered i.e. the
record is entered in my table.
so how can i achieve this????
should i use php or mysql to achieve this??
which is more...
|
by: jason |
last post by:
I need to deposit and empty dummy value into a date/time field within Access
using INSERT. However, I pick up a data type error if I attempt to insert
a NULL value for this particular date time...
|
by: Gord |
last post by:
Many scripts and calendars call client side system time in order to
make presentations.
However, the client's time may be improperly set, if set at all,
and/or the relevant time may be from...
|
by: Lapchien |
last post by:
My table has a record called date/time - filled (not surprisingly) with a
date and time. I can format the date in one query field, also the time,
using format in an expression. My user wants me...
|
by: Joe User |
last post by:
Hi all....I have a feeling this is going to be one of those twisted query
questions, but here it goes anyways....
I want to generate a report that shows the chronology of events (represented
by...
|
by: Tu Quach |
last post by:
Hi there, how are you?
I have question, assume I have a text box and gave it a format
date&time, how can I check when it is empty or not empty I use the code as
if frm_form!txt_name1 = Null...
|
by: Notgiven |
last post by:
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...
|
by: phried1 |
last post by:
I have created a form and inserted the following tables:
Date Entered
Time Entered
Date Modified
Time Modified
Essentially how and where can I have these dates and times recorded so when the...
|
by: jamil616 |
last post by:
Hi all,
I have a form which is also built with a subform. In the form I add the date and time when a record is changed/added to a respective table in the database. So that, when the next...
|
by: dbrewerton |
last post by:
I'm kind of at a loss as to how to do this but I'm going to run it by you experts anyway. I'm looking to create a linechart that will specify information based on the following ranges:
Last 8...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |