amount of overlaping dates

Hello again,

I am searching for a way to identify the amount of simultanious date ranges.

Example:

array start=('1.12', '5.12', '9.12');
array end =('8.12', '12.12', '16.12');

Looks like this in a table:
start end
1.12 8.12
5.12 12.12
9.12 16.12

Obviously the first and last daterange do not overlap. So the amount of
overlaping bookings is 2. But how to identify this with PHP?!

Any ideas?

Thank you for any help on this!!

Merlin

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: amount of overlaping dates

2009/12/3 Merlin Morgenstern :

> I am searching for a way to identify the amount of simultanious date rang=
es.
>
> Example:
>
> array start=3D('1.12', '5.12', '9.12');
> array end =3D('8.12', '12.12', '16.12');
>
> Looks like this in a table:
> start =A0 end
> 1.12 =A0 =A08.12
> 5.12 =A0 =A012.12
> 9.12 =A0 =A016.12
>
> Obviously the first and last daterange do not overlap. So the amount of
> overlaping bookings is 2. But how to identify this with PHP?!

Store the start and end times of each event in an SQL table.

SELECT COUNT(*) FROM `event` WHERE `start` <=3D NOW() AND `end` >=3D NOW()

gets you the number of events that are happening right now.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: amount of overlaping dates

--------------010101020202080101020100
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

David Otton wrote:
> 2009/12/3 Merlin Morgenstern :
>
>
>> I am searching for a way to identify the amount of simultanious date ranges.
>>
>> Example:
>>
>> array start=('1.12', '5.12', '9.12');
>> array end =('8.12', '12.12', '16.12');
>>
>> Looks like this in a table:
>> start end
>> 1.12 8.12
>> 5.12 12.12
>> 9.12 16.12
>>
>> Obviously the first and last daterange do not overlap. So the amount of
>> overlaping bookings is 2. But how to identify this with PHP?!
>>
>
> Store the start and end times of each event in an SQL table.
>
> SELECT COUNT(*) FROM `event` WHERE `start` <= NOW() AND `end` >= NOW()
>
> gets you the number of events that are happening right now.
>

That is what I thought first, too! But this does not work correct as
there might be a booking starting for example tomorrow. There needs to
be free place for the entire booking period.

I am a bit further now, but still stuck.

So far I could pull out all dates from the database that are within the
range:
SELECT * FROM `datetest` WHERE '2009-12-06' between start and end OR
'2009-12-13' between start and end

Now the trick would be to find overlaps. Here is an example:

I have a table with following booking info:

start end
2009-12-01 2009-12-08
2009-12-05 2009-12-12
2009-12-09 2009-12-16

I want to find out the first possible booking range for 14 day period
begining from 2009-12-06 at the earliest where a max of 3 bookings are
present. The result schould be: 2009-12-06

First I am pulling out all dates between the desired range:

SELECT *
FROM `datetest`
WHERE '2009-12-06'
BETWEEN START AND END OR '2009-12-13'
BETWEEN START AND END
LIMIT 0 , 30

Now the tricky parts starts where I do not know how to find out that the
first daterange in the table and the last daterange do not overlap.

Any ideas?

--------------010101020202080101020100--

Re: amount of overlaping dates

2009/12/3 Merlin Morgenstern :

> That is what I thought first, too! But this does not work correct as there
> might be a booking starting for example tomorrow. There needs to be free
> place for the entire booking period.

Ah, of course. I see the problem now. It's an odd situation, because
you don't seem to care if room A is available for the first half of
the period, and room B is available for the second half - as long as
the number of rooms never goes above 3. Checking for a single room
being available continuously for that entire period would be a lot
simpler.

If your resolution is 1 minute, then I'd run the query for every
minute of the event. 5 minute or 15 minute resolution would mean doing
far fewer queries. If the event count ever goes above 3, you can
short-circuit. Something like (pseudo-code)...

function is_room_available($start, $end, $resolution, $max) {
for($i = $start; $i < $end; $i+=$resolution)
{
$count = SELECT COUNT(*) FROM `event` WHERE `start` <= $i AND
`end` >= $i
if($count => $max) {
return false;
}
}
return true;
}

But I think turning it around might be simpler. Think:

I have three rooms. For each room, can it accept a 4 hour booking
starting at time $time?

Then the number of queries is a function of the number of rooms,
rather than the length of the meeting.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php