Sean, you definately got me :)
Your solution is simpler and much more elegant!
Had the visual aids here on paper, but I'm not as good
with the ascii art as you.
I definately learned something!
Thanks!
-Mike
--- [EMAIL PROTECTED] wrote:
> 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))
> > >
>
=== message truncated ===
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
