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

Phil Turmel philip at turmel.org
Tue May 31 10:00:53 CDT 2005


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)

HTH,

Phil



More information about the thelist mailing list