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.

Show previous replies

A

Anonymous

this comes back with there are no rooms available for your stay when I know there are no bookings in the database at all for the dates I entered

A

Anonymous

that did sort it thanks.

Still having problems. I've used this code to try and get the room_type, which is called rt_name to display if it's not booked. However, I get no returns from the database.

Thanks for your help
<cfparam name="PageNum_qGetRoomByDate" default="1">
<cfquery name="qGetRoomByDate" datasource="Ray">
SELECT rt_name, booking_confirmed, booking_start, booking_end, booking_made, booking_id, rt_id
FROM room_type, booking
WHERE booking.booking_start <= #CreateODBCDate(form.arrivalDate)#
AND booking.booking_end >= #CreateODBCDate(form.departDate)#
AND booking.room_type_id = room_type.rt_id
</cfquery>
<cfset MaxRows_qGetRoomByDate=10>
<cfset StartRow_qGetRoomByDate=Min((PageNum_qGetRoomByDate-1)*MaxRows_qGetRoomByDate+1,Max(qGetRoomByDate.RecordCount,1))>
<cfset EndRow_qGetRoomByDate=Min(StartRow_qGetRoomByDate+MaxRows_qGetRoomByDate-1,qGetRoomByDate.RecordCount)>
<cfset TotalPages_qGetRoomByDate=Ceiling(qGetRoomByDate.RecordCount/MaxRows_qGetRoomByDate)>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns=" http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<p>

</p>

<table border="1">
<tr>
<td>rt_name</td>
<td>booking_start</td>
<td>booking_end</td>
</tr>
<cfif qGetRoomByDate.RecordCount IS 0>
<cfoutput query="qGetRoomByDate" startRow="#StartRow_qGetRoomByDate#" maxRows="#MaxRows_qGetRoomByDate#">
<tr>
<td>#qGetRoomByDate.rt_name#</td>
<td>#qGetRoomByDate.booking_start#</td>
<td>#qGetRoomByDate.booking_end#</td>
</tr>
</cfoutput>
<cfelse>
<cfoutput> There are no roooms available for your stay</cfoutput>
</cfif>
</table>
<p> </p>
</body>
</html>

E

efecto747

Participating Frequently

Your example would appear to have the if/else condition at the end the wrong way round - should it be like this perhaps?

A

Anonymous

this is the entire error message I get

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][SQL Native Client][SQL Server]Invalid column name 'rt_id'.

The error occurred in C:\ColdFusion8\wwwroot\Rayanne\searchroomdate.cfm: line 5

3 : FROM room_type, booking
4 : WHERE booking.booking_start <= #CreateODBCDate(form.arrivalDate)#
5 : AND booking.booking_end >= #CreateODBCDate(form.departDate)#
6 : AND booking.rt_id = room_type.rt_id
7 : </cfquery>

--------------------------------------------------------------------------------

SQLSTATE 42S22
SQL SELECT * FROM room_type, booking WHERE booking.booking_start <= {d '2007-09-15'} AND booking.booking_end >= {d '2007-09-20'} AND booking.rt_id = room_type.rt_id
VENDORERRORCODE 207
DATASOURCE Ray

Resources:
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.

Browser Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; InfoPath.2; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30)
Remote Address 127.0.0.1
Referrer http://localhost:8500/rayanne/datesearch.cfm
Date/Time 11-Sep-07 05:46 AM

Stack Trace (click to expand)

E

efecto747

Participating Frequently

ok - so both tables, booking and room_type, have a field called rt_id?

A

Anonymous

I've tried a few thoughts people have suggested and come up with the following code.

<cfquery name="qGetRoomByDate" datasource="Ray">
SELECT *
FROM room_type, booking
WHERE booking.booking_start <= #CreateODBCDate(form.arrivalDate)#
AND booking.booking_end >= #CreateODBCDate(form.departDate)#
AND booking.rt_id = room_type.rt_id
</cfquery>

When I use the code, I get this error. However, there is a column called rt_id in the database

The error occurred in C:\ColdFusion8\wwwroot\Rayanne\searchroomdate.cfm: line 5

3 : FROM room_type, booking
4 : WHERE booking.booking_start <= #CreateODBCDate(form.arrivalDate)#
5 : AND booking.booking_end >= #CreateODBCDate(form.departDate)#
6 : AND booking.rt_id = room_type.rt_id
7 : </cfquery>

Just wondering why I get this error

E

efecto747

Participating Frequently

what is the exact error message? You've only listed the detail that appears after the error message.

C

cf_dev2

Inspiring

> If it's booked then it doesn't show.

As your table only contains booked rooms, you could only search for rooms already booked. You could then use the query record count to determine whether the room should be displayed as available. If the record count is 0 the room is available. Otherwise the room is already booked.

paross1 has the right idea, but you may need a few more conditions. Take a moment to think about your logic and what conditions you should search. Obviously you need to check

- If the Room is booked for the entire #form.arrivalDate# to #form.departDate# period

But other conditions are

- If the Booking.DepartDate is the same as the #form.arrivalDate#? or ..
- If the Room is booked for part of the #form.arrivalDate# to #form.departDate# period?

Of course this assumes the arrivalDate/departDate fields are dates only. If they contain a time as well you will need to adjust your sql.

A

Anonymous

the booking table contains a start date and end date of when the customer arrives and when they depart (start and end dates).

What I need to is have the room displayed only if it's not booked between the start and end date.

If it's booked then it doesn't show.

A

Anonymous

"What I need to is have the room displayed only if it's not booked between the start and end date."

DOES THIS MEAN THAT YOU NEED TO DISPLAY ALL THOSE ROOMS WHICH ARE NOT BOOKED DURING A SPECIFIC DATES
,THEN
SELECT ROOM_NUMBER FROM BOOKING WHERE
arrivalDate BETWEEN CreateODBCDate(form.arrivalDate) AND departDate BETWEEN CreateODBCDate(form.departDate)
AND customer.booking_id <> booking.booking_id

I ASSUMED THAT THE BOOKING_ID IS UNIQUE AND WOULD ONLY BE PRESENT IN THE CUSTOMER TABLE IF THE BOOKING IS DONE.

I HOPE THIS HELPS .IF IT DOESN'T PLEASE SEND THE TABLE DESCRIPTION.

jJ

C

cf_dev2

Inspiring

> If the room is not available it's not listed. If it is, it's then listed.

Do you mean if the booking table contains a record for a specific arrival/depart date range that means a room is not available?

A

Anonymous

Not sure what you mean, if I have an arrival is this not the only. What I want to do is search the database for an arrival date. Say Sept 10 2007 and the departure date say Sept 17 2007.

If the room is not available it's not listed. If it is, it's then listed.

Maybe I'm not using the right SQL but I don't think I can two arrival dates and two departure dates

Any help would greatly appreciated

P

paross1

Participating Frequently

quote:

Maybe I'm not using the right SQL...

My point, exactly. How about something like this?

SELECT *
FROM customers, booking
WHERE booking.arrivalDate <= CreateODBCDate(form.arrivalDate)
AND booking.departDate >= CreateODBCDate(form.departDate)
AND customer.booking_id = booking.booking_id

Phil

P

paross1

Participating Frequently

Well, for one thing, when checking that a date is BETWEEN two dates, you should actually use two dates. For example, you are asking if arrivalDate is BETWEEN CreateODBCDate(form.arrivalDate) and what? Same with departDate. In other words, your syntax for the use of BETWEEN is not correct. It should be something like this:

WHERE arrivalDate BETWEEN aDate1 AND aDate2
AND departDate BETWEEN dDate1 AND dDate2..... etc.

Phil

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