How to find vacant rooms between given date range

Question

1.00/5 (1 vote)

See more:

hi,

I am currently working on the hospital room booking system. In this, I want to display vacant rooms from the given date range. Even if single day vacant it should pick up. Kindly share your ideas or a sample query

I am having two tables one room master and another is a temp_booking table.

I want to find out from room master which rooms are vacant for a given date range. Room master contains room no, room category and temmp_booking is used for booking which contains room no, book_from_date and book_to_date

Eg:
Start date - 01/01/2018
end date - 01/15/2018
room category - 'Deluxe suite'
Total rooms in this category - 6 (Ds-1, DS-2......DS-6)

Room No Category book_from_date book_to_date
DS-1 Deluxe Suite 01/01/2018 01/15/2018
Ds-2 Deluxe Suite 01/04/2018 01/15/2018
Ds-3 Deluxe Suite 01/01/2018 01/10/2018

from the above table, I want to display Ds-2 and Ds-3 and also the remaining rooms DS-4, DS-5 and also 6 when I check for room availability from 01/01/2018 to 01/15/2018.

Because DS-2 is vacant from 01/01/2018 to 01/03/2018 and DS-3 is vacant from 01/11/2018 to 01/15/2018 and remaining 3 rooms have no booking. Hope you got it. Want to list this on the priority basis.

What I have tried:

SQL

SELECT r.Room_No
FROM       room_master AS r
WHERE room_catg = 'Deluxe Suite'
AND r.room_no NOT IN (SELECT t1.room_no
                          FROM Room_Master t1
                          INNER JOIN temp_booking t2 ON t1.room_no = t2.room_no
                           WHERE NOT (Book_To_Date < '01/01/2018') OR ([Book_To_Date] > '01/15/2018'))

This query displaying the rooms which are fully vacant for the given date range.

Posted 27-Dec-17 19:55pm

Member 13597123

Updated 28-Dec-17 21:26pm

Patrice T

v4

Add a Solution

Comments

OriginalGriff 28-Dec-17 2:15am

We can't: we have no idea what your tables looks like, what data they contain, how they are related. We have no idea what that query produces that is different from what you want.

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
Use the "Improve question" widget to edit your question and provide better information.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 7870345 · Accepted Answer · 2017-12-28T21:26:00

Hello:
With the schema that you gives for your database it is not posible to obtein the result tat you pretend.
The problem consists in that you want to get all the rooms provided that at least one day in the range is vacation, but there is no table that contais all the days that exists in a range. If your range is 01/01/2018- 01/15/2018 it´s not suficient that exists/not exists a reservation in that range, because is posible to exists a reservation from 01/04/2018 to 01/15/2018.

I think that if you use a stored procedure instead of a Select stament you can achive your goal.

So create a stored procedure that uses the parameters fromDate, toDate, and whatever more parameters to suite your requirements (room_catg for example).
In this procedure create a temporal cursor containing just one column room_no in order to contains the rooms that it´s sure that contains at least one day vacation (this cursor it´s empty at the begining).
Then do a while loop to "walk" from fromDate to toDate, day by day. Inside this loop you can use the query of your example (modified to consider just the range of only the day of this step of the loop). This will give you the rooms that are vacation in this only day. Proceed to insert this in the temporal cursor.
When the loop ends, in the cursor you can obtein the list of the rooms (probabily with duplicates) that at least have a vacation in the days that are between fromDate and toDate.

I hope this will help you