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

[Burhan Khalid]

Phil Turmel wrote:
> Burhan,
> 
> My cartesian join / subquery solution can be extended with grouping to 
> return ALL future gaps, with an arbitrary minimum gap size...
> 
>>>> 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.
>>>>
> 
> Maybe something like this, with $hall and $gapneeded substituted using 
> Your Favorite Scripting Language (tm):
> 
> SELECT DATE_ADD(before.end_date,INTERVAL 1 DAY) AS gapstart,
>   MIN(DATEDIFF(after.start_date,before.end_date)-1) AS gapsize
>   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)>$gapneeded
>   GROUP BY DATE_ADD(before.end_date,INTERVAL 1 DAY)
> 

Holy bananas Phil. That is possibly the most complex query I have seen 
in my life :) I'll give it a shot and post the results.  I also came up 
with another idea that I'm going to play around with.