[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