[thelist] Iterate through result set - Is this possible? (SQL)

Burhan Khalid thelist at meidomus.com
Tue May 31 05:41:46 CDT 2005


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.




More information about the thelist mailing list