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]
