On 5 Oct, 14:43, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Fri, 05 Oct 2007 14:58:48 +0200, Jerry Stuckle
>
>
>
> <jstuck...@attglobal.net> wrote:
> > Rik Wasmus wrote:
> >> On Fri, 05 Oct 2007 06:58:22 +0200, subtenante
> >> <zzsubtenant...@gmail.com> wrote:
>
> >>> On Tue, 02 Oct 2007 18:02:58 -0700, ½aßrain <mccb...@gmail.com> wrote:
>
> >>>> Hi Guys,
>
> >>>> I have a table of events with a start and finish datetime.
>
> >>>> I am trying to write a php function that will return the next earliest
> >>>> start time that an event will fit. Will this be something that I can
> >>>> do just in mysql?
>
> >>>> or something much more complex?
>
> >>>> anyone have any tips?
>
> >>> SELECT * FROM mytable
> >>> WHERE start > NOW()
> >>> ORDER BY start
> >>> LIMIT 1
> >> That's for the next event indeed. I gathered the OP meant 'next empty
> >> slot'. In which case these 'slots' should either have a table or the OP
> >> should tell us more about them.
>
> > I tried something like this several years ago in DB2 for a conference
> > room reservation system. I wasn't overly successful. I did get it to
> > work with temporary tables and recursive SQL, but the whole thing was a
> > huge mess. Even as a SP it was complicated and tough to understand.
>
> > I finally just did a simple query to fetch allocated timeslots, ordered
> > by the time, and had a C function find the first available slot. The
> > code came out much cleaner and more understandable.
>
> Yup, I'd hughly prefer 'or the OP should tell us more about them' portion
> of the answer. Aside from 'taken' timeslots, what are his requirements? Do
> we take 'working hours' into account for this system or is it continuous,
> are 'slots' of an arbitrary length or set/rounded to something, what are
> the other requirements for an event to 'fit', etc. etc.
>
> One could device a query getting all upcoming 'holes' in a schedule, plus
> a 'slot' of a predefined length after the last ending time in the table,
> filter them for length, and find the first slot with a particular minimum
> length in the results. How this would look exactly would heavily depend on
> the actual needs.
> --
> Rik Wasmus

Is there a technical objection to a purely MySQL solution? Otherwise,
wouldn't (a tidier version of) something like this work...?

CREATE TABLE `bookings` (
`booking_id` tinyint(4) NOT NULL auto_increment,
`start` datetime NOT NULL,
`end` datetime NOT NULL,
PRIMARY KEY (`booking_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO `bookings` VALUES (6, '2007-10-01 00:00:00', '2007-10-05
00:00:00');
INSERT INTO `bookings` VALUES (8, '2007-10-09 00:00:00', '2007-10-14
00:00:00');
INSERT INTO `bookings` VALUES (10, '2007-10-15 00:00:00', '2007-10-18
00:00:00');
INSERT INTO `bookings` VALUES (12, '2007-10-23 00:00:00', '2007-10-26
00:00:00');
INSERT INTO `bookings` VALUES (14, '2007-11-01 00:00:00', '2007-11-05
00:00:00');


SELECT x.end 'from', y.start 'to', DATEDIFF( y.start, x.end )
my_interval
FROM (

SELECT t1.booking_id booking_id_a, COUNT( t1.booking_id ) AS rank_a,
t1.end
FROM bookings t1
LEFT JOIN bookings t2 ON t1.booking_id >= t2.booking_id
GROUP BY booking_id_a
)x
LEFT JOIN (

SELECT t1.booking_id booking_id_b, COUNT( t1.booking_id ) AS rank_b,
t1.start
FROM bookings t1
LEFT JOIN bookings t2 ON t1.booking_id >= t2.booking_id
GROUP BY booking_id_b
)y ON x.rank_a = y.rank_b -1
HAVING my_interval >=4
ORDER BY my_interval
LIMIT 1 ;