On 6 Oct, 12:01, strawberry <zac.ca...@gmail.com> wrote:
> 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 theOP
> > >> 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 wasa
> > > 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 dependon
> > 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 ;

Hmm, I joined on booking_id, but that was wrong. I guess the join
should be on the start date...

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.start >= t2.start
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.start >= t2.start
GROUP BY booking_id_b
)y ON x.rank_a = y.rank_b -1
HAVING my_interval >=4
ORDER BY my_interval
LIMIT 1 ;