[thelist] SQL Dates Problem

Burhan Khalid thelist at meidomus.com
Tue Apr 12 08:40:51 CDT 2005


Joshua Olson wrote:
>>-----Original Message-----
>>From: Burhan Khalid
>>Sent: Tuesday, April 12, 2005 9:03 AM
>>
>>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.
> 
> 
> Burhan,
> 
> Here's an approach that may work for you:
> 
> 1.  Convert the integer timestamps to date fields so that we can use them as
> comparison.  Make sure the time portion of the date field is midnight.
> 2.  Enumerate all possible days (from today to a reasonably far date in the
> future) in another table.
> 3.  Join with the enumeration table to determine the minimum available date
> for each hall that doesn't have an event. *

Unless I didn't understand what you meant, if a hall has no events, then 
the first available date is tommorrow.


More information about the thelist mailing list