[thelist] SQL Dates Problem

Burhan Khalid thelist at meidomus.com
Wed Apr 13 00:54:13 CDT 2005

Ken Schaefer wrote:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : From: thelist-bounces at lists.evolt.org [mailto:thelist-
> : bounces at lists.evolt.org] On Behalf Of Burhan Khalid
> : Subject: [thelist] SQL Dates Problem
> : 
> :    I'm sure there is a better way to do this.  Can anyone suggest a
> : better approach?  Perhaps there is some SQL magic that can get me the
> : correct number in one try?  I'm using MySQL.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> I could write you something, but I don't think it'll work in mySQL, unless
> mySQL supports subselects now.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> :    I'm also using a similar rudimentary approach to 
> : finding out of a new event conflicts with any event 
> : in a hall.  For this, I'm getting all the start/end 
> : date pairs for each hall's events, then checking if the new
> : event's date pairs conflict.  The idea here is that 
> : if an event's start and end dates are in between 
> : start and end dates for another event, then
> : they conflict.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>    |------Event------|
> |-----|            |------|
>        |--------|
>  |----------------------|
> The above diagram shows all the possible ways a proposed event would conflict
> with an existing event (use a monospaced font to view helps)
> If you'll notice, every conflicting event has a start date (or time) prior to
> the existing event's end date and an end date after the existing event's
> start date.
> So
> SELECT (whatever)
> FROM Events
> WHERE (newEventStartDate) < EventStartDate
> AND (newEventEndDate) > EventStartDate
> would return any conflicting, existing events. That query will work in mySQL
> (at least something does...) :-)

I never thought about going about it this way. Atleast its something to try.

For the other problem of finding available dates, after a few whiteboard 
sessions -- the idea came up to find the difference between the end and 
start dates of an event -- if this difference is equal to one day, then 
there is a possible free date.  If I wanted to find 3 free dates, then I 
could just do some math and mod the resulting difference.  Can anyone 
see any problems with this approach?

As if this wasn't causing me enough problems -- there is a new 
requirement that an event can span more than one hall.  So if there is 
an event that is going to be two weeks long and takes place in halls 
5,6,7 -- I need to find the earliest date where all three halls have 14 
days free.  So now I'm back to square one.

Oh the joys of date programming :|

Thanks for all the ideas,

More information about the thelist mailing list