October 3, 2011 at 1:32 am
I am having a trouble in querying my database.. I have a table :
**Room**
+----------------------------------+
|RoomNo---RoomStatusID---RoomTypeID|
| 1 --- 1 --- 1 |
| 2 --- 1 --- 1 |
+----------------------------------+
**ClientRoom**
+--------------------------------------------------------------------+
|ClientRoomNo---RoomNo--------ArrivalDate-------------DepartureDate | 1 --- 1 ---- 2011-10-03 1:00:00 ---- 2011-10-03 4:00:00|
| 2 --- 1 ---- 2011-10-03 5:00:00 ---- 2011-10-03 8:00:00|
+--------------------------------------------------------------------+
If I use this query =
SELECT Room.RoomNo, RoomType.RoomType
FROM Room INNER JOIN
RoomType ON Room.RoomTypeNo = RoomType.RoomTypeNo
FULL OUTER JOIN ClientRoom ON Room.RoomNo = ClientRoom.RoomNo
WHERE
(((ClientRoom.ArrivalDate <= '10-03-2011 1:00:00' AND
ClientRoom.ArrivalDate <= '10-03-2011 5:00:00') AND
(ClientRoom.DepartureDate <= '10-03-2011 1:00:00' AND
ClientRoom.DepartureDate <= '10-03-2011 5:00:00'))
OR Room.RoomStatusId = 1)
It would return
**ClientRoom**
+--------------------------------------------------------------------+
|ClientRoomNo---RoomNo--------ArrivalDate-------------DepartureDate|
| 2 --- 1 ---- 2011-10-03 5:00:00 ---- 2011-10-03 8:00:00 +--------------------------------------------------------------------+
Because it reached up to 5:00am in the morning. But when I change the Arrival and Departure to.. 5:00 - 8:00.. RoomNo 1 is still in the results. I already tried NOT BETWEEN 🙁
October 3, 2011 at 2:56 am
If you provide table scripts, sample data and desired output based on the sample data, I'm sure lots of people will jump in and solve your issue. Until you do that, I highly doubt somebody is doing the work for you.
Please, read the first article linked in my signature line and find out how to post an effective question on the forums and get the best help.
-- Gianluca Sartori
October 3, 2011 at 1:39 pm
Based on how I understand your question you are getting the results you are asking for because you are using <= AND >=. Those are INCLUSIVE so if any dates hit on a boundary you are going to get that row. I would change one of the comparisons in each case to not use the <= OR >=.
In which case do you want the row returned? Then I can possibly recommend what you should do.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply