Results 1 to 3 of 3
  1. #1
    Join Date
    May 2014
    Posts
    1

    Unanswered: Can not get correct query

    Hi,
    I have below query

    SELECT room.* FROM room
    WHERE roomid NOT IN
    (SELECT roomid FROM reservations res
    WHERE
    '5/20/2014 12:00:00 AM' > res.CheckInDate
    and '5/28/2014 12:00:00 AM' <= res.checkoutdate
    and '5/28/2014 12:00:00 AM' >= res.checkindate
    and res.roomcurrentstatus='reserved')

    In my reservation table 3 rooms are reserved and checkindate were
    5/16/2014, 5/19/2014,5/28/3014. checkoutdate were, 5/23/2014,5/27/2014, 5/31/2014. from above query i got none, But when I change
    '5/20/2014 12:00:00 AM' > res.CheckInDate to '5/8/2014 12:00:00 AM' > res.CheckInDate - one room come out in result. is there anybody can help me to get correct query for hotel reservation system. Thanks.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    SELECT room.* FROM room
    WHERE 
        roomid NOT IN
            (SELECT roomid FROM reservations res
             WHERE	
                 '5/8/2014 12:00:00 AM' <= res.checkoutdate and 
                 '5/28/2014 12:00:00 AM' >= res.checkindate and
                 res.roomcurrentstatus = 'reserved')
    Hope this helps.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. You should know it, since it is used in so many ISO standards.

    This is minimal polite behavior on SQL forums. Since you have no manners, I will guess you want to use DATE. Then you want to use BETWEEN.

    I have below query

    SELECT Hotel.*
    FROM Hotel
    WHERE room_nbr
    NOT IN (SELECT room_nbr
    FROM Reservations AS R
    AND '2014-05-20' > R.checkin_date*--- why?
    AND '2014-05-28' BETWEEN R.checkin_date*AND R.checkout_date*
    AND R.room_status = 'reserved';

    In my reservation table 3 rooms are reserved
    checkin_date were 2014-05-16, 2014-05-19,2014-05-28.
    checkout_date were, 2014-05-23, 2014-05-27, 2014-05-31.

    Which dates make pairs?? If you had posted DDL and ample data instead a vague narrative, we would know. Here is my guess.

    CREATE TABLE Reservations
    (room_nbr CHAR(5) NOT NULL,
    checkin_date DATE NOT NULL,
    checkout_date DATE NOT NULL,
    CHECK (checkin_date <= checkout_date),
    PRIMARY KEY (room_nbr, checkin_date ));

    INSERT INTO Reservations
    VALUES
    ('101', '2014-05-16', '2014-05-23'),
    ('102', '2014-05-19', '2014-05-27'),
    ('103', '2014-05-28', '2014-05-31');

    >> Is there anybody can help me to get correct query for hotel reservation system. <<

    Can you give us a spec?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •