SQL Query to output present guests in hotel

Question

Consider the above relations. I'm trying to list all guests and dependants who are currently staying in the hotel, meaning they are present in the rooms.

Here is my SQL QUERY:

SELECT g.FORENAME, g.SURNAME           
    FROM GUEST g
    INNER JOIN BOOKING b 
            ON g.GUESTID = b.GUESTID
    WHERE (b.DEPARTDATE > TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY') AND b.ARRIVEDATE < TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY'))
    UNION   
    SELECT d.FORENAME, d.SURNAME
    FROM DEPENDANT d
    INNER JOIN BOOKINGDEPENDANT bd 
            ON bd.DEPENDANTID = d.DEPENDANTID
    WHERE bd.BOOKINGID IN (SELECT b.BOOKINGID 
                            FROM BOOKING b
                            WHERE (b.DEPARTDATE > TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY') AND b.ARRIVEDATE < TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY')));

The query executes but displays no rows selected, when it is supposed to.

Here are my insert statements for BOOKING.

INSERT INTO BOOKING VALUES (2002, 5445, '19-MAR-2013', '10-JUN-2013', 250.00);

INSERT INTO BOOKING VALUES (2004, 3005, '19-MAR-2013', '21-MAR-2013', 250.00);

despite guest 5445 and 3005 meeting the where condition with their associated booking, no output is displaying. Im expecting the names of 5445 and 3005.

if I remove the below lines:

b.ARRIVEDATE < TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY')

a list of names would be displayed. but the logic is wrong because without arrivedate consideration, you wouldnt be able to detect the present guests.

My question to you is the above line incorrectly added? why is it not displaying those guest when they meet the condition. they are the present guest after all. What can I do?

JohnLBevan · Accepted Answer · 2013-03-21T01:39:59.833

4

I think the issue is you're comparing strings rather than dates.

'19-MAR-2013' < '21-MAR-2013' => TRUE
'10-JUN-2013' > '21-MAR-2013' => FALSE (as '10...' < '21...')

Hold your dates in your database as dates. Alternatively convert them to the format YYYY-MM-DD if you need to compare them as text (as this way the string comparison will match the date comparison.

NB: though the correct solution is to change your columns to hold the data as dates, below's a corrected version of the SQL which will work with the text columns (though nowhere near as efficient / correct):

SELECT g.FORENAME, g.SURNAME           
FROM GUEST g
INNER JOIN BOOKING b 
    ON g.GUESTID = b.GUESTID
WHERE CURRENT_DATE BETWEEN TO_DATE(b.ARRIVEDATE, 'DD-MON-YYYY') AND TO_DATE(b.DEPARTDATE, 'DD-MON-YYYY')

UNION   

SELECT d.FORENAME, d.SURNAME
FROM DEPENDANT d
INNER JOIN BOOKINGDEPENDANT bd 
    ON bd.DEPENDANTID = d.DEPENDANTID
WHERE bd.BOOKINGID IN 
(
    SELECT b.BOOKINGID 
    FROM BOOKING b
    WHERE CURRENT_DATE BETWEEN TO_DATE(b.ARRIVEDATE, 'DD-MON-YYYY') AND TO_DATE(b.DEPARTDATE, 'DD-MON-YYYY')
);

edited Mar 21 '13 at 01:39

answered Mar 21 '13 at 01:33

JohnLBevan

22,735
13
96
178

ps. see also http://stackoverflow.com/questions/10178292/comparing-dates-in-oracle-sql for more info on this topic – JohnLBevan Mar 21 '13 at 01:41
@JohnBevan hi John, my columns are dates as defined in CREATE table statements. in the query I did to_char having seen other discussions how people use them. but misunderstood the concept. as opposed to TO_CHAR what can I replace them with? or is your answer above the only solution – Hoody Mar 21 '13 at 01:44
Hey @MuminAli; if your columns are already dates just compare them to CURRENT_DATE - that already returns a date, so no need to convert it. i.e. `WHERE CURRENT_DATE BETWEEN b.ARRIVEDATE AND b.DEPARTDATE` – JohnLBevan Mar 21 '13 at 01:47
No rows selected is what it displays. initial problem in question. – Hoody Mar 21 '13 at 01:50
fixed :D working, it needed refreshing updated create tables. – Hoody Mar 21 '13 at 01:53

SQL Query to output present guests in hotel

1 Answers1