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.
Find available bookings between 2 dates
Moderator: General Moderators
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.
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.
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.
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
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
)
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??
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??