[thelist] SQL Dates Problem
Burhan Khalid
thelist at meidomus.com
Tue Apr 12 08:02:50 CDT 2005
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
More information about the thelist
mailing list