Burhan Khalid wrote: > Ken Schaefer wrote: > >> Hi, >> >> Your current requirements are ambiguous (because you can have multiple >> events >> that have a start date > 7 days after any given event start date) > > > Yes I understand this, but I need the first available 7 day gap for a > given hall, not a listing of all the events whose gap is 7 days or more. > > Sorry I didn't make this clear before. > >> Can you post a set of sample data, and what your expected output is? >> If you >> can provide that, then we can write the necessary SQL (if it's >> possible) to >> generate the output. > > > I'll have to get remote access, but I'll post as soon as I can get it. Here is the information : CREATE TABLE `events` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL default '', `start_date` date NOT NULL default '0000-00-00', `end_date` date NOT NULL default '0000-00-00', `etype` int(11) NOT NULL default '1', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Sample data ( id | name | start_date | end_date ): 13 | burhan event | 2005-06-08 | 2005-06-10 15 | burhan | 2005-05-30 | 2005-06-05 16 | Expo | 2005-06-28 | 2005-07-22 CREATE TABLE `halls_events` ( `id` int(11) NOT NULL auto_increment, `hallid` int(11) default NULL, `eventid` int(11) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Sample data ( hallid | eventid ): 2 | 13 4 | 13 2 | 15 2 | 16 *Note that an event can take place in two halls. Now, what I'm trying to find is the first available 7 day gap for each hall. So for hall 2, the latest free date is 2005-06-11, or one date after the end of event 13. My idea was to find a set of events where the gap between the end date and start date is 7 days, limit it to the first event. I just can't seem to wrap my head around the logic.