Hi C.F.
I'm new to the list, so please excuse me if I'm
answering out-of-turn from the pro's here.
I think the answer is to also check if the
BusyTime_start is between start and end of the
attempted scheduled.
ie:
WHERE
(TAID = 1) AND
(('2005-10-27 17:30' BETWEEN starts AND ends) OR
('2005-10-27 18:10' BETWEEN starts AND ends) OR
(starts BETWEEN '2005-10-27 17:30' AND '2005-10-27
18:10))
I think you only need to check starts (see case 5
below) because the other case is picked up by the
first two checks. (see case 6 below)
The way I see it, you have 6 cases, but some
simplification can be done, as I have noted:
1) attempted schedule time lies outside of busy times
(return 0 = NOT BUSY)
2) attempted schedule end time lies between busy times
(return !0 = busy)
3) attempted schedule begin time lies between busy
times (return !0 = busy)
4) attempted schedule begin AND end time lie between
busy times (return !0 = busy)
[this case is a special case of #2 and #3, so it
really disappears!]
5) Busy Time begin time lies between attempted
schedule begin and end times (the problem)
6) Busy Time end time lies between attempted schedule
begin and end times (the problem)
You must check either case 5 or 6 to be sure to catch
the 'attempted schedule wraps busy schedule' case. If
not, you obviously see the problem.
I think that only case 5 or 6 needs to be checked (not
both) because if one of those is not true, then case
1, 2, 3 [or 4] (the only one's left!) must be true.
Hope that helps, and wasn't too confusing!
-Mike McFadden
--- "C.F. Scheidecker Antunes" <[EMAIL PROTECTED]>
wrote:
> 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.
__________________________________
Start your day with Yahoo! - Make it your home page!
http://www.yahoo.com/r/hs
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
