Find available bookings between 2 dates

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Find available bookings between 2 dates

Post 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.
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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.
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post 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??
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

So if I understand correctly you want that SQL to accept a date range rather than a static date?
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

Thats exactly right.
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

...bump...
I've been playing around a little bit but havent had much luck with this - anybody else got any ideas??
Thanks
Post Reply