[thelist] SQL Dates Problem

[Jay Blanchard]

[snip]
   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.
[/snip]

The earliest availability date would be the date that the hall is
available, that could be as early as tomorrow. Since you do not have all
dates in a column you may have to do a little magic to get what you want
just using a SQL statement. Consider that you would have to choose start
dates minus one day that are not equal to today...(my syntax may be off
a little and the queries are not complete)

SELECT DATE_SUB(MIN(e.start_date), INTERVAL 1 DAY)
FROM events e LEFT OUTER JOIN hall_events h
ON(e.id = h.eventid)
WHERE DATE_SUB(MIN(e.start_date), INTERVAL 1 DAY) <> NOW()

This is a bad query because it will only give you the day before the
earliest start date unless that date is today. If the earliest start
date is the 16th it should give you the 15th since today is the 12th.
But the hall is actually available on the 13th, 14th and 15th, so this
query will not meet your requirements. You can try this...

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS FirstAvailable
FROM events e
WHERE DATE_ADD(NOW(), INTERVAL 1 DAY) < MIN(e.start_date)

But this is only good for tomorrow.

I also see that you really only need two tables, one for halls and one
for events, which should make your life much more simple and is much
more scalable if you add halls.

halls = id, name, description
events = id, hallid, name, start_date, end_date (as normal as it needs
to be)

I know that I have not solved your problem, hopefully I have given you
more to work with.