August 26, 2012 at 11:24 am
[font="Times New Roman"][/font]
I'm a university student and I'm developing a database for a hotel system ,now I'm stack when I try to check rooms that are available for a specific date,room_status and type of a room.I'm using SQL server 2008, I have two table that m checking on which are : dbo.RoomBooking(Booking_ID,Room_Number,Arrival_date,Departure_date) , dbo.Rooms(Room_Number,Room_type,Room_status)
I hv tried this query: create proc GetAvailableRoom
(@RT_Name char(15), @ArriveDate Date, @DepDate Date)
AS SELECT Room_no from Rooms r ,Room_Type rt
WHERE r.Room_Type_Code = rt.Room_Type_Code and
Room_no NOT IN (Select Room_no FROM Room_Bookings
Where Chck_in_Date Between @ArriveDate and @DepDate and
Chck_out_Date between @ArriveDate and @DepDate)
and rt.Room_Type_Name = @RT_Name and Room_Status = 'Avail'
go
August 26, 2012 at 11:37 am
Nobody's going to help you now that you started spamming the forums.
That smells homework and you could at least have posted DDL and DML code and what you have so far and not try to make people make your homework for you.
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
August 26, 2012 at 12:13 pm
What's the table definitions? What's the sample data? What's the problem with the query?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2012 at 12:21 pm
@
I ddnt knw posting the same post many time is spamming ,I was trying to get help as quick as possible. The project tht I'm doing is industrial not a school work, by writing m a university student,I wanted you to knw tht I dnt have that much stills.
August 26, 2012 at 12:39 pm
mfundotogu (8/26/2012)
@I ddnt knw posting the same post many time is spamming ,I was trying to get help as quick as possible. The project tht I'm doing is industrial not a school work, by writing m a university student,I wanted you to knw tht I dnt have that much stills.
this type of question appears on this site quite regularly....try searching the site for "hotel"
as an example
http://www.sqlservercentral.com/Forums/Topic1259572-392-1.aspx
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 26, 2012 at 12:44 pm
create table Rooms
(Room_no char(4) primary key not null,
Room_Status varchar(20) not null,
Room_Type_Code char(4) foreign key references Room_Type(Room_Type_Code))
go
Data in the Table.
1011AvailSTD1
1012AvailSTD1
2011AvailDLX2
Create table Room_Bookings
(Booking_ID char(8) NOT NULL ,
Room_no char(4) NOT NULL Foreign KEY REFERENCES Rooms,
Adult_Num int ,
Child_Num int,
Chck_in_Date date,
Chck_out_Date date,
Rack_Rate decimal(7,2),
Guest_ID char(13)
)
go
Booking_ID R_No Arrv_Date Dep_Date
083044124012 2012-08-15 2012-08-25750.009210021234567
0837441240122012-08-20 2012-09-28750.009210021234567
0855412 10122012-08-15 2012-09-30450.009201112345678
1234567810122012-08-22 2012-08-23450.000987654321123
1234567810122012-08-25 2012-09-23450.000987654321123
The query does nt return correct room_Nos if the Arrv_Date and Dep_date are within the range of booked dates.
e.g Arrv_Date:2012-08-22 ,Dep_date:2012-09-20 ,it will return room 4012 and 1012 ofwhich they are booked by tht date.
August 26, 2012 at 12:52 pm
So what should it return?
http://www.sqlservercentral.com/articles/Best+Practices/61537/
p.s. Hotel booking systems are often used as exercises for database courses because they're very hard to get right.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2012 at 12:59 pm
It must return rooms that are not booked at those dates.
August 26, 2012 at 1:10 pm
And those are? Be specific, list exactly what must be returned (as the article I linked explains)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2012 at 1:51 pm
Booking_ID R_No........... Arrv_Date .............Dep_Date
083044124014........... 2012-08-15 ..........2012-08-25750.009210021234567
083744124011........... .....2012-08-20 ..........2012-09-28750.009210021234567
0855412 2013..................2012-08-15......... 2012-09-30450.009201112345678
123456783014................2012-08-22 ..........2012-08-29450.000987654321123
123456781015 ................2012-08-20..........2012-09-23450.000987654321123
Arrival Date :2012-08-30 Departure date 2012-09-10
It should return Room_No: 4014......2012-08-15....2012-08-25
------------------------- 3014......2012-08-22.....2012-08-29
but it also return Room_No: 2013.....2012-08-15.....2012-09-30
August 27, 2012 at 7:55 am
mfundotogu (8/26/2012)
create table Rooms(Room_no char(4) primary key not null,
Room_Status varchar(20) not null,
Room_Type_Code char(4) foreign key references Room_Type(Room_Type_Code))
go
Data in the Table.
1011AvailSTD1
1012AvailSTD1
2011AvailDLX2
Create table Room_Bookings
(Booking_ID char(8) NOT NULL ,
Room_no char(4) NOT NULL Foreign KEY REFERENCES Rooms,
Adult_Num int ,
Child_Num int,
Chck_in_Date date,
Chck_out_Date date,
Rack_Rate decimal(7,2),
Guest_ID char(13)
)
go
Booking_ID R_No Arrv_Date Dep_Date
083044124012 2012-08-15 2012-08-25750.009210021234567
0837441240122012-08-20 2012-09-28750.009210021234567
0855412 10122012-08-15 2012-09-30450.009201112345678
1234567810122012-08-22 2012-08-23450.000987654321123
1234567810122012-08-25 2012-09-23450.000987654321123
The query does nt return correct room_Nos if the Arrv_Date and Dep_date are within the range of booked dates.
e.g Arrv_Date:2012-08-22 ,Dep_date:2012-09-20 ,it will return room 4012 and 1012 ofwhich they are booked by tht date.
If you actually want help you need to do some of the work yourself. Gail has provided a link already to the best practices when posting questions.
Take a look at this post and ask yourself if you would be able to use this. The Rooms table has a foreign key reference to a table you didn't provide. Your sample data should be insert statements. There are lots of people willing to help but very few willing to do the work for you. If this is school you don't learn anything by other providing you answers, if this is work we are not getting paid to do your job for you. In either case, we are willing to help if you are willing to put in the effort.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 27, 2012 at 8:34 am
Overlapping of times.
The overlapping of times is often handled wrong.
Suppose we have a table with booked_start and booked_finish.
And you have a begintime and endtime for which you want the 'overlapping' occurences.
First we start with situations which are NOT overlapping:
If begintime >= booked_finish then there is not overlapping occurence.
If endtime <= booked_start then there is no overlapping.
So not overlapping is:
(begintime >= booked_finish) OR (endtime =< booked_start)
So overlapping is the inverse of that:
NOT ((begintime >= booked_finish) OR (endtime <= booked_start))
This works out to be
(begintime < booked_finish) and (endtime > booked_start)
Remarks.
1. Here the times are considered to be continuous.
2. When a the finish is at exactly the same time as a begintime, this is not considered to be overlapping.
3. So when working with dates, this should be first converted to times, where the booking is defined as from the booked_start (including) up to the booked_finish (excluding). In this way it does not matter if the data is in minutes, seconds, hours, days, weeks, month's or whatever granulity is used for the time registration.
Hope this will help with understanding the request and not giving away the solution.
Offcourse you should check te logic used here yourself. (Don't believe me !).
Ben Brugman
August 28, 2012 at 7:35 am
Get/find articles/books by Itzik Ben-Gan that discuss the Gaps and Islands problems. He (and others) provide a range of solutions to this scheduling problem.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 14, 2014 at 1:31 am
codebyo (8/26/2012)
Nobody's going to help you now that you started spamming the forums.That smells homework and you could at least have posted DDL and DML code and what you have so far and not try to make people make your homework for you.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply