Mike, you did a wonderful job at analysis (identifying the 6 cases) but I
think a series of visual clues would have made a simpler query more
obvious. Here's how I understand the issue (I am a more visual thinker)
Start with the case of needing to see if a new record (NR) overlaps with
an exisiting record (ER). In this case both NR and ER would have start
dates and end dates so intermediate calculations are avoided (speed
boost).
If the ER is scheduled to finish BEFORE The start of the NR, it the
situation looks like this:
fig a.
ER: |---------|
NR: |-----------|
If the ER is scheduled for a time AFTER the NR the situation looks like
this:
fig b.
ER: |----------|
NR: |--------------|
Leaving us with the 4 kinds of overlaps. Overlaps 1 and 2 are when the NR
starts before the ER but ends within the ER's scheduled time and vice
versa
fig c.
ER: |-------| or |-----|
NR: |------| |-----|
Overlaps 3 and 4 are if one schedule is completely surrounded by the
other:
fig d.
ER: |------------| or |--|
NR: |-----| |---------|
Using these visual aids, we can notice a pattern of all of the situations
where a conflict exists:
a) the starting date of one event is BEFORE the ending date of the other
AND
b) the ending date of one event is AFTER the starting data of the other.
In either situation where BOTH terms are NOT true, there will be no
overlap. So to find your overlapping events check for both comparisons to
be true. Notice that sequencing doesn't matter so long as you compare
opposite ends of the events.
That simplifies the original query to:
SELECT count(TAID) as total
FROM tbl_schedule
WHERE (TAID = 1)
AND '2005-10-27 17:30' < ends
AND '2005-10-27 18:10' > starts;
I have also seen this analysis stated more rigorously using Boolean
Algebra. I don't have a link to it but it may be interesting for some of
you if you took the time to go find it.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Michael McFadden <[EMAIL PROTECTED]> wrote on 10/27/2005 08:24:54 PM:
> 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]
>
