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

[Phil Turmel]

Allow me to revisit before Ken corrects me... 1st (and 2nd) try had the 
wrong ORDER clause...  See adjustment below.

Phil Turmel wrote:
> Burhan Khalid wrote:
> 
>> Burhan Khalid wrote:
>>
>>> Ken Schaefer wrote:
>>>
>>>> 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.
>>
>>
>> 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.
>>
> 
> Burhan,
> 
> Maybe something like this, with $hall substituted using Your Favorite 
> Scripting Language (tm):
> 
> SELECT before.end_date, after.start_date
>     FROM (SELECT events.id, events.end_date
>     FROM events INNER JOIN hall_events
>         ON events.id=hall_events.eventid
>     WHERE events.end_date > CURDATE()
>         AND hall_events.hallid=$hall)
>     AS before,
>     (SELECT events.id, events.start_date
>     FROM events INNER JOIN hall_events
>         ON events.id=hall_events.eventid
>     WHERE events.end_date > CURDATE()
>         AND hall_events.hallid=$hall)
>     AS after
>     WHERE DATEDIFF(after.start_date,before.end_date)>7
       ORDER BY before.end_date,
           DATEDIFF(after.start_date,before.end_date)
>     LIMIT 1
> 
> ** Untested **. needs MySQL v4.1 or later
> 
> This doesn't return anything where no sufficient gap exists... then you 
> must query for the last end_date for the hall.
> 
> HTH,
> 
> Phil
>