I think there are four cases to consider (hopefully the "picture" will
come through okay).
starttime|----------|endtime The time span in consideration
*-----------------* Case 1: ta has mtg that
starts before starttime, mtg ends between starttime and endtime 1
*------------------------------* Case 2: mtg starts before
starttime, mtg ends after endtime 2
*-----* Case 3: mtg starts and mtg
ends between startime and endtime 3
*---------------* Case 4: mtg starts between
starttime and endtime, mtg ends after endtime 4
However the four can be reduced to two:
a. A meeting starts before the starttime and ends after the starttime
(cases 1 and 2)
or
b. A meeting starts between the starttime and the endtime (cases 3 and
4).
So to check if the TA is busy between 17:30 and 18:10
SELECT count(TAID) as total
FROM tbl_schedule
where TAID = 1
and ((starts <'2005-10-27 17:30' and ends >'2005-10-27 17:30')
or (starts >= '2005-10-27 17:30' and starts < '2005-10-27 18:10'))
Regards,
Donna
"C.F. Scheidecker Antunes" <[EMAIL PROTECTED]>
10/27/2005 05:31 PM
To
[email protected]
cc
Subject
{Spam?} MySQL and dates puzzle
Hello all,
I have a TA table to record TA UNAVAILABLE times.
This table is quite simple. It has a TAID number, a start date and an
end date.
tbl_schedule {
TAID integer,
starts datetime,
ends datetime }
A valid entry would be a TA whose id is 1 and between 17:00 and 18:00 he
is busy.
So:
1,'2005-10-27 17:00:00','2005-10-27 18:00:00'
Each ta can have more than one entry per day. He might be a busy TA and
have a lot
of meetings scheduled. The meetings do not have to be 1 hour length,
they can be 5 or 10 minutes.
So something like this would also be valid:
1,'2005-10-27 17:05:00','2005-10-27 17:10:00'
Now, I need to check, given a start and end dates, if that would overlap
with some record already present
in the database.
If I want to know if the TA is busy between 17:30 and 18:10 I could I
issue something like this:
SELECT count(TAID) as total FROM tbl_schedule where (TAID = 1) AND
(('2005-10-27 17:30' BETWEEN starts AND ends) OR ('2005-10-27 18:10'
BETWEEN starts AND ends))
It would return a number not zero as total if the dates are between the
registered database. However, this does not work properly.
Here's a case when it does not work:
Say I want to check between 14:00 and 20:00. The TA is busy from 17:00
and 18:00, hence if I try to schedule a meeting that will go
from 14:00 to 20:00 with the statement above it would return 0 as total.
This is not good because I need to know that given 14:00 to 20:00 that
would
not overlap with any previous engagement on the database. Since the TA
is busy from 17:00 to 18:00 I must know that I cannot schedule anything
like
that.
Can anyone help me on this issue? How can I check given a start and end
datetime that it does not overlap with what is in the database?
Thanks,
C.F.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
CONFIDENTIALITY NOTICE:This email is intended solely for the person or
entity to which it is addressed and may contain confidential and/or
protected health information. Any duplication, dissemination, action
taken in reliance upon, or other use of this information by persons or
entities other than the intended recipient is prohibited and may violate
applicable laws. If this email has been received in error, please notify
the sender and delete the information from your system. The views
expressed in this email are those of the sender and may not necessarily
represent the views of IntelliCare.
