[thelist] SQL Dates Problem

[Burhan Khalid]

Hello Everyone:

   I'm sure there is a simple way to do this, but I've been staring at 
the problem too long and I can't get out of this mental rut.

   In the simplest case, there are three tables, halls, events, and 
halls_events.

   halls = id, name, description
   events = id, name, start_date, end_date (both are integer timestamps)
   halls_events = id, hallid, eventid

   I can't figure out the best way to find out the earliest availablity 
date for each hall.  The earliest date is one day from the latest end 
date for an event in a particular hall.

   My current approach is this :

   1. Find out if there are any events in a particular hall.  For this, 
I'm searching halls_events for a matching hallid.  If there aren't any 
events, then the latest available date is +1 from today.

   2. If there are some events for a particular hall I get the list of 
eventids.  Then I get all their end_dates from events.  Then I get the 
max end_date and then add 1 day to this to get the latest available free 
date for a hall.  This whole process involves a lot of queries, 
especially if a hall as a lot of events.  Not to mention it doesn't 
trigger free those dates that occur between two pairs.  For example, if 
a hall has one event from jan - feb; and another event from jun - july, 
then my current approach will tell me that first available free date is 
the first of August, not the first of March.

   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'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.

Help :(

Regards,
Burhan