[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