Check room availability in hotel system

  • [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

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • @

    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.

  • 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

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • It must return rooms that are not booked at those dates.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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/

  • 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

  • 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

  • @ben.brugman, thanks my brother you information real helped me to get /correct the problem I was experiencing.

    Now my program is working .

  • 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