[thelist] Iterate through result set - Is this possible? (SQL)
Burhan Khalid
thelist at meidomus.com
Tue May 31 10:38:35 CDT 2005
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.
More information about the thelist
mailing list