How Do I Get The Unreserved Rooms For A Particular Time Period

Question

0.00/5 (No votes)

See more:

hi.i am novice with sql server oracle and I have a question:
I have 2 tables: tab Rooms
ID
RoomName tab Reservations
ID
DateStart
DateEnd
Status
Comment ID_Rooms Basic idea that I have a list of rooms in tab
Rooms, and reservations for that rooms in tab
Reservations. DateStart and DateEnd are
SmallDateTime type. My problem is to make sql query which will get 2
input parameters (DateStart and DateEnd),
and will return all rooms which are NOT reserved
in period between DateStart and DateEnd. Thanks!

Posted 4-Aug-15 4:12am

Member 11885166

Add a Solution

Comments

F-ES Sitecore 4-Aug-15 10:41am

This is a very popular homework subject and a very popular question. Do a google search and you'll find various solutions.

Member 11885166 4-Aug-15 10:43am

sorry,bt i m nt able find out the solution,please help if u know

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)

E.F. Nijboer · Answer 1 · 2015-08-04T04:29:00

Solution 1

Easiest way is using a temporal auxiliary that holds dates for a large period of time. For example, 1-1-2000 up until 1-1-2050. You can then easily select all dates from the auxiliary table and join it with the tables holding the room information/reservations. There are also some nice books on this subject like Joe Celko's thinking in sets.

Good luck!

Posted 4-Aug-15 4:29am

E.F. Nijboer

Comments

Member 11885166 4-Aug-15 10:31am

i cant get u..can u please write the sql query fr d same,please

E.F. Nijboer 4-Aug-15 10:56am

It is not what this site is about. The people on codeproject (including me) aren't going to write the code for you. We try to help you in the right direction but it still is up to you to do the programming. otherwise you just need to hire a developer somewhere.

Member 11885166 4-Aug-15 11:00am

so can u tell some sites from where i can referr

E.F. Nijboer 4-Aug-15 11:08am

http://pratchev.blogspot.nl/2007/12/auxiliary-tables.html

The example for Calendar creates an auxiliary for 2008 and also adds workday information. After that is shows to easily count all the workdays. You could use the same sort of mechanism.