May 27, 2008 at 6:54 am
I have made an application for hotels that contains a table called ReservationRooms containing, inter alia, ResNo, ResDate, ResRoom, CheckinDate, CheckoutDate, and Status. When a new reservation is being entered, the application loops through existing reservations for each room and uses this formula ascertain whether a room is vacant or not for the stated date range:
SELECT COUNT(RESNO)
FROM RESERVATIONROOMS
WHERE RESROOM = ROOMNO
AND
(
DATEIN BETWEEN CHECKINDATE AND (CHECKOUTDATE - 1)
--CHECK ONLY UNTIL THE PREVIOUS DAY
--MAY BE CHECKED IN ONCE IT HAS BEEN CHECKED OUT
OR
DATEOUT BETWEEN (CHECKINDATE + 1) AND CHECKOUTDATE
--CHECK ONLY FROM AFTER CHECKIN
--MAY BE CHECKED OUT THE SAME DAY AS IT IS CHECKED IN
)
This formula served me well for a long time, although I had not thought through all the possible
situations when it will yield wrong results. It so happened that if a new reservation "straddled" an existing reservation's checkin and checkout dates, the respective room would show up as vacant, e.g.: if Room 112 is booked from 24Dec2008 to 26Dec2008 and a clerk checks for rooms vacant from 20Dec2008 to 31Dec2008 the formula would work thus:
[Code]IF 20Dec2008 BETWEEN 24DEC2008 AND 25DEC2008 --FALSE
OR 31DEC2008 BETWEEN 25DEC2008 AND 26DEC2008 --FALSE [/Code]
so I extended the formula by adding a reverse test as follows:
SELECT COUNT(RESNO)
FROM RESERVATIONROOMS
WHERE RESROOM = ROOMNO
AND
(
(
DATEIN BETWEEN CHECKINDATE AND (CHECKOUTDATE - 1) --CHECK ONLY UNTIL THE PENULTIMATE DAY AS A ROOM
OR --MAY BE CHECKED IN ONCE IT HAS BEEN CHECKED OUT
DATEOUT BETWEEN (CHECKINDATE + 1) AND CHECKOUTDATE --CHECK ONLY FROM THE DATE AFTER CHECKIN AS A ROOM
) --MAY BE CHECKED OUT THE SAME DAY AS IT IS CHECKED IN
OR
(
CHECKINDATE BETWEEN DATEIN AND (DATEOUT - 1) --CHECK ONLY UNTIL THE PENULTIMATE DAY AS A ROOM
OR --MAY BE CHECKED IN ONCE IT HAS BEEN CHECKED OUT
CHECKOUTDATE BETWEEN (DATEIN + 1) AND DATEOUT --CHECK ONLY FROM THE DATE AFTER CHECKIN AS A ROOM
)
)
This traps all situations I can think of right now, although I will welcome any tips on foolproofing it.
However, the reason I am posting here is that having read a lot about how tally tables help improve date-processing queries, I would like to know how I can incorporate a tally table to enhance this query. I am certain that only applying a tally table will give me the number of days in a given date range that are already taken by existing bookings, for example, the above instance is passing a date range of 11 days to check for vacancy whilst the room has been booked for only 2 days out of the 11.
May 27, 2008 at 9:40 am
If you are really "looping" then a Tally table can help you to get rid of that Loop. If so, then please post the whole SQL procedure, not just this fragment.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2008 at 9:49 am
That said, here is how I would do something like this:
Select Room
From Rooms R
WHERE Not Exists (Select * from ReservationRooms RR
Where R.Room = RR.ResRoom
And CHECKINDATE < @DATEOUT
And CHECKOUT >= @DATEIN
)
I included some of the outer query, because it is necessary to understand the logic of the inner query.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2008 at 12:29 am
Hi,
Will this be useful or this also fails anywhere/
select count(RESNO) from RESERVATIONROOMS
where checkin not between Datein and Dateout and checkout not between DateIn and DateOut
Regards,
Rajesh
May 28, 2008 at 2:22 am
Hi, we do this all the time.. so I think you can steal my logic.. it's simple enough to not need a tally table?
SELECT COUNT(RESNO)
FROM RESERVATIONROOMS
WHERE RESROOM = ROOMNO
AND (DATEIN <= (CHECKOUTDATE - 1)
AND DATEOUT >= (CHECKINDATE + 1))
Examples of why it should work:
-- EXAMPLE FORMAT
DATEIN <= CHECKOUTDATE - 1
DATEOUT >= CHECKINDATE + 1
-- Overlap example.. does not allow booking
20081220 <= 20081225 - 1 -- TRUE
20081231 >= 20081224 + 1 -- TRUE
-- Booking prior to existing.. allows booking
20081220 <= 20081225 - 1 -- TRUE
20081222 >= 20081224 + 1 -- FALSE
-- Booking after existing.. allows booking
20081225 <= 20081225 - 1 -- FALSE
20081228 >= 20081224 + 1 -- TRUE
May 28, 2008 at 6:07 am
raja_saminathan (5/28/2008)[hrWill this be useful or this also fails anywhere/
select count(RESNO) from RESERVATIONROOMS
where checkin not between Datein and Dateout and checkout not between DateIn and DateOut
Rajesh, sorry, this fails to catch cases where CheckIn-CheckOut spans (surronds) DateIn-DateOut.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2008 at 6:10 am
janine: Except for the +/-1's this is the same logic as mine.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 30, 2008 at 3:37 am
FORMULA#1 - BARRY
Select Room
From Rooms R
WHERE Not Exists
(
Select * from ReservationRooms RR
Where R.Room = RR.ResRoom
And CHECKINDATE < @DATEOUT
And CHECKOUT >= @DATEIN
)
FORMULA#2 - JANINE
SELECT COUNT(RESNO)
FROM RESERVATIONROOMS
WHERE RESROOM = ROOMNO
AND (@DATEIN <= (CHECKOUTDATE - 1)
AND @DATEOUT >= (CHECKINDATE + 1))
FORMULA#3 - GOODGUY
SELECT COUNT(RESNO)
FROM RESERVATIONROOMS
WHERE RESROOM = ROOMNO
AND
(
(
@DATEIN BETWEEN CHECKINDATE AND (CHECKOUTDATE - 1)
OR
@DATEOUT BETWEEN (CHECKINDATE + 1) AND CHECKOUTDATE
)
OR
(
CHECKINDATE BETWEEN @DATEIN AND (@DATEOUT - 1)
OR
CHECKOUTDATE BETWEEN (@DATEIN + 1) AND @DATEOUT
)
)
TEST RESULTS
CHECKINDATE CHECKOUTDATE @DATEIN @DATEOUT ACTUAL F#1 F#2 F#3
20DEC2008 01JAN2009 24DEC2008 26DEC2008 Y Y Y Y
20DEC2008 01JAN2009 21DEC2008 22DEC2008 Y Y Y Y
24DEC2008 26DEC2008 20DEC2008 01JAN2009 Y Y Y Y
21DEC2008 22DEC2008 20DEC2008 01JAN2009 Y Y Y Y
20DEC2008 22JAN2009 18DEC2008 21DEC2008 Y Y Y Y
18DEC2008 21DEC2008 20DEC2008 22JAN2009 Y Y Y Y
18DEC2008 21DEC2008 21DEC2008 24JAN2009 N Y* N N
21DEC2008 24JAN2009 18DEC2008 21DEC2008 N N N N
20DEC2008 24JAN2009 18DEC2008 21DEC2008 Y Y N* Y
May 30, 2008 at 3:48 am
Goodguy, I know my code fails on one of those tests but I kept the date manipulation as per original logic. I don't know why but seems he is intentionally allowing the checkin date to be as it is.
To fix the error in your example (assuming OP's logic is also in error) simply:
FORMULA#2 - JANINE
SELECT COUNT(RESNO)
FROM RESERVATIONROOMS
WHERE RESROOM = ROOMNO
AND (@DATEIN <= (CHECKOUTDATE - 1)
AND @DATEOUT >= (CHECKINDATE))
.. though this "fails" on anotehr test instead.
I think at this point the question should be; what is the business logic? Can you have a person check out and another person check in the same day? etc.
May 30, 2008 at 5:08 am
[Quote]Goodguy, I know my code fails on one of those tests but I kept the date manipulation as per original logic. I don't know why but seems he is intentionally allowing the checkin date to be as it is.
To fix the error in your example (assuming OP's logic is also in error) simply:
FORMULA#2 - JANINE
SELECT COUNT(RESNO)
FROM RESERVATIONROOMS
WHERE RESROOM = ROOMNO
AND (@DATEIN <= (CHECKOUTDATE - 1)
AND @DATEOUT >= (CHECKINDATE))
.. though this "fails" on another test instead. I think at this point the question should be what is the business logic? Can a person check out and another person check in the same day? etc.[/Quote]
Yes, in hotels, departing guests have to check out by a fixed hour every morning, between 8am and 11am. New arrivals are checked in about an hour later in order to allow for rooms to be cleaned etc.
I am Goodguy and I am the OP. My logic is not in error, as I have demonstrated in the test results for the three formulas, mine consistently returns correct results.
I am just looking for a way to do this
(1) more efficiently
(2) get a count of the number of days common to the two date ranges.
June 3, 2008 at 1:59 pm
It's simple enough to correct mine, given this clarification:
Select Room
From Rooms R
WHERE Not Exists (Select * from ReservationRooms RR
Where R.Room = RR.ResRoom
And CHECKINDATE < @DATEOUT
And CHECKOUT > @DATEIN
)
Janine's can also be corrected if you really want it in the COUNT(*) form:
SELECT COUNT(RESNO)
FROM RESERVATIONROOMS
WHERE RESROOM = ROOMNO
AND @DATEIN < CHECKOUTDATE
AND @DATEOUT > CHECKINDATE
Both of these should be significantly faster and they have the additional advantage of being SARGE-able, which means that they can use indexes for searching.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 4, 2008 at 12:09 am
April 21, 2009 at 3:06 am
This example is exactly what I'm working with for apartment availabilty search.
So, basically I just need to extend the code so that it returns all the properties that are available between those two dates. Just as with hotels, departure date can equal arrival date. But instead of complicating the code, my booking are nights so you book the night of the 12th and someone else can book the night of the 13th, so there should be no overlapping dates.
My existing SP returns a list of properties from the RentalProperty table from a query
I then want to use the results from this to check each one in the list against the dates.
Tables:
RentalProperty
RentalPropertyBookingCalendar
linked on the RentalPropertyID int field
these are the dates I will send in to check against:
@QstartDate
@QendDate
I ended up with this, but doesn't work:
SELECT *
FROM dbo.RentalProperty R
WHERE (AgentID = 93) AND (NOT EXISTS
(SELECT *
FROM RentalPropertyBookingCalendar RR
WHERE R.RentalPropertyID = RR.RentalPropertyID AND ((FromDate 12 / 06 / 2009) OR
(FromDate > 15 / 06 / 2009 AND ToDate < 12 / 06 / 2009))))
April 21, 2009 at 8:46 am
richard (4/21/2009)
I ended up with this, but doesn't work:
You'll have to give us more information than that. What would help us are table definitions (as CREATEs), sample data (as INSERTs), what results you are getting and what results you want.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 21, 2009 at 9:01 am
Thanks, I have solved it now.
I used and modified the code from this post and it works a treat.
....AND
WHERE (NOT EXISTS
(SELECT *
FROM RentalPropertyBookingCalendar RR
WHERE R.RentalPropertyID = RR.RentalPropertyID AND ((FromDate @QStartDate) OR
(FromDate > @QEndDate AND ToDate < @QStartDate))))
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply