Re: amount of overlaping dates

David Otton wrote:

2009/12/3 Merlin Morgenstern <merlin_x@fastmail.fm>:

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?