Page 1 of 1

Find available bookings between 2 dates

Posted: Sun Jul 23, 2006 4:44 am
by mcccy005
It doesnt help that my mind isn't on this stuff at the moment, but I've been scratching my head trying to work out how to select boats which are not fully booked between two dates.

SO the essential things I have are:
Table bookings
booking_id
boat_id (references boats.boat_id)
date_booked_from
date_booked_until

A boat can have 2 bookings on the same date. (Ie. date_booked_until can equal date_boked_from) in another booking.

So I want to select the boats WHERE there is at least one two days between bookings. (Eg. one booking finishes on 1/1/06; and only select this boat as being available if the next 'date_booked_from' is 3/1/06 (or greater). )

Thanks.

Posted: Mon Jul 24, 2006 7:22 pm
by mcccy005
Has anyone got ANY suggestions whatsoever??

I can probably use PHP and sql to do this by creating an array of days the size of the number of days between each; then fill in the booked days of the array for each boat and then if there is empty cells in the array; then there that boat IS available during the period.
However, this will take up significant processing/memory resources (and be much slower; especially for much larger record sets); which is why I really want to find a way to do this using only an sql query!

Cheers.

Posted: Tue Jul 25, 2006 3:02 am
by GM
You are trying to examine a relationship between records in the same table, which always makes life difficult...

Let me get it straight: You are looking for boats available on a given date BUT... they should only be classed as available if there is a gap of one clear day until the next booking - correct?

EDIT: changed wording slightly.

Posted: Tue Jul 25, 2006 5:03 am
by jamiel

Code: Select all

SET @date_to_check='2006-07-08';

SELECT DISTINCT
	boat_id
FROM
	boats
WHERE
	@date_to_check
NOT BETWEEN
	date_booked_from AND date_booked_until
AND
	boat_id
NOT IN
	( SELECT boat_id FROM boats 
	WHERE @date_to_check BETWEEN date_booked_from AND date_booked_until
	)
You can add a clause to the final WHERE to sort out the '2 day' thing you want.

Posted: Tue Jul 25, 2006 8:13 pm
by mcccy005
Thats very close; but not quite.

A user does a search to see which boats are available to be hired between 1/1/07 and 1/2/07 for example.

Basically most boats are hired from Mon->Friday; and the next booking begins Friday->Monday.
The 2 day thing means this. A boat is hired Mon->Friday. If there is another booking 2 days later (ie. on Sunday), don't bother showing this boat as available.

Just thinking.....I COULD use your code in a php for loop, incrementing the day by 1 each time however.
(Eg. @date_to_check = '2006-07-08';
<<your code goes here>>
Then do the same again, but increment the day by 1 (so long as it is less than the last date the user is searching for (1/2/07 as I used above)??
This again would be labourous and slow should large date ranges be looked at?






I'm not that great with sql; but I'm guessing 'DISTINCT' ensures I don't select the same boat_id multiple times??

Posted: Wed Jul 26, 2006 3:21 am
by jamiel
So if I understand correctly you want that SQL to accept a date range rather than a static date?

Posted: Wed Jul 26, 2006 7:15 am
by mcccy005
Thats exactly right.

Posted: Mon Aug 07, 2006 8:47 am
by mcccy005
...bump...
I've been playing around a little bit but havent had much luck with this - anybody else got any ideas??
Thanks