A

Anonymous

Question

Searching on a date range

Forum|Forum|18 years ago
September 6, 2007
19 replies
2946 views

I have two tables I need to search both based on a startdate and enddate.

I was thinking something like

select *
from customers, booking
where arrivalDate BETWEEN CreateODBCDate(form.arrivalDate) AND departDate BETWEEN CreateODBCDate(form.departDate)
AND customer.booking_id = booking.booking_id

I haven't been able to get this to work any ideas in the right direction would be greatly appreciated

Database access

This topic has been closed for replies.

A

Anonymous

What if I was to search on a column called confirmed. If this is true for the date range entered don't display this room. If it's false, meaning there is no confirmation for this date range then display room.

Is this what I should be thinking about and working on or is there a better way

A

Anonymous

Which conditions are you talking about ?
The "Criteria" are basically Examples.

You would like to use the Query
<cfquery name="qryAvailableRooms" datasource="Ray">
SELECT rt_name
FROM room_type
WHERE rt_id IN (
SELECT room_type_id as rt_id
FROM booking
WHERE booking_start > #CreateODBCDate(form.departDate)#
OR booking_end < #CreateODBCDate(form.arrivalDate)#
)
</cfquery>

jJ

A

Anonymous

How do I put this comditions into the code as I haven't done this kind of coding before

A

Anonymous

What other types of logic would I need

C

cf_dev2

Inspiring

If you only want to display rooms that are available for the full date range requested, start with conditions like the ones below. There may be overlapping or other conditions you need to to check

A. Booking arrivalDate is between #form.arrivalDate# and #form.departDate#

Example:
#form.arrivalDate# = 12/17/2007
#form.departDate# = 12/27/2007

Booking arrivalDate = 12/18/2007
Booking departDate = 12/29/2007

B. Booking departDate is between #form.arrivalDate# and #form.departDate#

Example:
#form.arrivalDate# = 12/17/2007
#form.departDate# = 12/27/2007

Booking arrivalDate = 12/12/2007
Booking departDate = 12/24/2007

C. Booking arrivalDate is on or before #form.arrivalDate# and
Booking departDate is on or after #form.departDate#

Example:
#form.arrivalDate# = 12/17/2007
#form.departDate# = 12/27/2007

Booking arrivalDate = 12/15/2007
Booking departDate = 12/31/2007

Other things to consider are

D. If Booking departDate is the same as #form.arrivalDate#, do you want your application
to consider the room available or not?

E. If there isn't a single room available for the full date range, do you want to
consider booking 2 or more different rooms. Probably not, but its something to think
about

Example:
If a room is requested for

#form.arrivalDate# = 12/17/2007
#form.departDate# = 12/27/2007

.. and none of the rooms are available for that entire range, but:

And RoomA is available from 12/17/2007 to 12/21/2007
And RoomB is available from 12/22/2007 to 12/27/2007

A

Anonymous

Sorry for my previous post.

Try taking a different approach where the booking dates do not fall under range of FORM.Dates with a OR condition
<cfquery name="qryAvailableRooms" datasource="Ray">
SELECT rt_name
FROM room_type
WHERE rt_id IN (
SELECT room_type_id as rt_id
FROM booking
WHERE booking_start > #CreateODBCDate(form.departDate)#
OR booking_end < #CreateODBCDate(form.arrivalDate)#
)
</cfquery>

form.arrivalDate:12/10/07
form.departDate 12/15/07

Criteria 1-
booking_start=12/09/07
booking_end=12/20/07
WHERE 12/09/07 > 12/15/07
OR 12/20/07 < 12/10/07
NOT Available

Criteria 2
booking_start=12/09/07
booking_end=12/12/07
WHERE 12/09/07 > 12/15/07
OR 12/12/07 < 12/10/07
NOT Available

Criteria 3
booking_start=12/11/07
booking_end=12/14/07
WHERE 12/11/07 > 12/15/07
OR 12/14/07 < 12/10/07
NOT Available
Criteria 3 would not work for the following
"WHERE booking_start <= #CreateODBCDate(form.arrivalDate)#
AND booking_end >= #CreateODBCDate(form.departDate)#"

Criteria 4
booking_start=12/11/07
booking_end=12/20/07
WHERE 12/11/07 > 12/15/07
OR 12/20/07 < 12/10/07
NOT Available

Criteria 5
booking_start=12/02/07
booking_end=12/07/07
WHERE 12/02/07 > 12/15/07
OR 12/07/07 < 12/10/07
Available

Criteria 6
booking_start=12/20/07
booking_end=12/24/07
WHERE 12/20/07 > 12/15/07
OR 12/24/07 < 12/10/07
Available

jJ

C

cf_dev2

Inspiring

jhutchdublin wrote:
> I tried the code and get a 500 Internal Server error.

Turn off friendly http error messages http://support.microsoft.com/kb/294807

efecto747's code is a start, but as I mentioned before you may need to check other conditions. Like if a room is already reserved for part of the arrival/depart date range

For example
#form.arrivalDate# = 12/17/2007
#form.departDate# = 12/27/2007

If RoomA is already booked from 12/21/2007 to 12/31/2007. It's not available for the full date range. But this code would not detect that condition and would incorrectly indicate that RoomA was available from 12/17/2007 through 12/27/2007

WHERE booking_start <= #CreateODBCDate(form.arrivalDate)#
AND booking_end >= #CreateODBCDate(form.departDate)#

A

Anonymous

I tried the code and get a 500 Internal Server error. Won't even tell me what the error is

C

cf_dev2

Inspiring

First, what are you actually trying to display? I get the sense its not just available rooms.

A

Anonymous

Kind of what kind of logic do I need then

E

efecto747

Participating Frequently

ok, I think I understand whats going on..

The query, qGetRoomByDate, is selecting all rooms that are booked for the selected period.
You want to then display a list of available rooms that are NOT booked for the selected period.

If this is the case, you'd need to query a table containing all available rooms and exclude the rooms selected in your first query - something like the query below should do it. I've simplified the select and assumed the room_type table is where your room data is stored:

A

Anonymous

I'm not sure what you're saying.

C

cf_dev2

Inspiring

> There are no roooms available for your stay

Perhaps this is what is throwing me off. You seem to be trying to display available rooms. Meaning rooms that have not already been booked. But your query retrieves unavailable rooms. To figure out what rooms are available you would need additional logic. Make sense?

C

cf_dev2

Inspiring

I agree with efecto747. The logic is off. From what you've described the bookings table contains unavailable rooms. So trying to use that code (alone) to display available rooms doesn't make sense.

<cfif qGetUnavailableRooms.RecordCount IS 0>
....
<cfelse>
There are no roooms available for your stay
</cfif>

Show more replies

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded