Available dates query

[webbo34]

Hi guys,

I'm having a bit of trouble finding querying a table with "start" and "end" dates.

Consider the following tables;

Event table
------------

id | eventName
1 Event 1
2 Event 2

Event Bookings Table
---------------------

id | eventId | bookingStartDate | bookingEndDate |
1 1 01/05/08 14/05/08
2 2 07/05/08 12/05/08
3 2 08/06/08 24/06/08

What I need to do is find all events that are NOT booked between two given dates; for example I might enter the dates 20/05/2008 to 01/06/2008 and would expect to get only event 1 (because event 2 is not fully available during the date period).

Here is where it gets tricky; I also need to add flexibility to this. So for example, if the date range entered is 20/05/2008 to 01/06/2008 and the flexibility selected was 2 days, then it would need to check 2 days either side of the date range as follows;

18/05/2008 - 30/05/2008 (inclusive)
OR
19/05/2008 - 31/05/2008 (inclusive)
OR
20/05/2008 - 01/06/2008 (inclusive)
OR
21/05/2008 - 02/06/2008 (inclusive)
OR
22/05/2008 - 03/06/2008 (inclusive)

Ideally, I would like to keep the query to one single MySQL query (if possible).

If anyone could help, I would be most grateful.

Kind Regards

Webbo

 

[webbo34]

I think I got it, but I'm not 100% sure. Could someone check and see if there is a better way to do this;

Note: for test purposes I wanted to find all event (id's) that were available in the selected range 04/05/08 to 06/05/08 with a 2 day flexibility;

SELECT DISTINCT `test_eventbook`.`eventId`
FROM
`test_eventbook`
WHERE NOT
(
(
(`test_eventbook`.`bookStart` <= '2008-05-03 00:00:00' AND `test_eventbook`.`bookEnd` >= '2008-05-03 23:59:59')
OR
(`test_eventbook`.`bookStart` <= '2008-05-04 00:00:00' AND `test_eventbook`.`bookEnd` >= '2008-05-04 23:59:59')
OR
(`test_eventbook`.`bookStart` <= '2008-05-05 00:00:00' AND `test_eventbook`.`bookEnd` >= '2008-05-05 23:59:59')
)
AND
(
(`test_eventbook`.`bookStart` <= '2008-05-04 00:00:00' AND `test_eventbook`.`bookEnd` >= '2008-05-04 23:59:59')
OR
(`test_eventbook`.`bookStart` <= '2008-05-05 00:00:00' AND `test_eventbook`.`bookEnd` >= '2008-05-05 23:59:59')
OR
(`test_eventbook`.`bookStart` <= '2008-05-06 00:00:00' AND `test_eventbook`.`bookEnd` >= '2008-05-06 23:59:59')
)
AND
(
(`test_eventbook`.`bookStart` <= '2008-05-05 00:00:00' AND `test_eventbook`.`bookEnd` >= '2008-05-05 23:59:59')
OR
(`test_eventbook`.`bookStart` <= '2008-05-06 00:00:00' AND `test_eventbook`.`bookEnd` >= '2008-05-06 23:59:59')
OR
(`test_eventbook`.`bookStart` <= '2008-05-07 00:00:00' AND `test_eventbook`.`bookEnd` >= '2008-05-07 23:59:59')
)
)


I know its complicated to read but hopefully someone out there can see what is going on

 

[webbo34]

I think I might have simplified the query down to this;

SELECT `test_eventbook`.`eventId`
FROM
`test_eventbook`
WHERE NOT
(
(`test_eventbook`.`bookStart` <= '2008-05-03 00:00:00' AND `test_eventbook`.`bookEnd` >= '2008-05-05 23:59:59')
AND
(`test_eventbook`.`bookStart` <= '2008-05-04 00:00:00' AND `test_eventbook`.`bookEnd` >= '2008-05-06 23:59:59')
AND
(`test_eventbook`.`bookStart` <= '2008-05-05 00:00:00' AND `test_eventbook`.`bookEnd` >= '2008-05-07 23:59:59')
)


I'll keep testing..

 

[webbo34]

OK, I think that did it.

Here is the MySQL query in its full working glory;


SELECT DISTINCT
`test_event`.`name`,
`test_event`.`id`
FROM
`test_event`
Left Join `test_eventbook` ON `test_event`.`id` = `test_eventbook`.`eventId`
WHERE NOT
(
(`test_eventbook`.`bookStart` <= '2008-05-03 00:00:00' AND `test_eventbook`.`bookEnd` >= '2008-05-05 23:59:59')
AND
(`test_eventbook`.`bookStart` <= '2008-05-04 00:00:00' AND `test_eventbook`.`bookEnd` >= '2008-05-06 23:59:59')
AND
(`test_eventbook`.`bookStart` <= '2008-05-05 00:00:00' AND `test_eventbook`.`bookEnd` >= '2008-05-07 23:59:59')
)
OR `test_eventbook`.`eventId` IS NULL


It was nice talking to myself!

 

[audiopro]

Don't worry, myself and I do it all the time

Keith

http://www.studiosoft.co.uk

 

[johnwm]

You could make it a lot easier if you use the BETWEEN operator - see

http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#operator_between

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints