Re: Hotel Booking dates subquery

Jan Hidders wrote:

>Scotty wrote:
>>
>>Your test data has a big problem in that you have the same room being
>>used on conflicting dates:
>
>That is irrelevant because in this test we are only looking if periods
>overlap. The room number is immaterial for that. If it makes you nervous
>anyway you can renumber them 1 through 6. Please turn your brain back on
>because otherwise you will learn nothing and your instructor won't like
>that.

Renaming the room_id 1-6 with your dates

BOOKING_ID ROOM_ID STARTDATE ENDDATE DURATION

---------- ---------- --------- --------- ----------
         1          1 01-NOV-02 05-NOV-02        100
         2          2 01-NOV-02 17-NOV-03        100
         3          3 01-NOV-02 30-NOV-02        100
         4          4 13-NOV-02 17-NOV-02        100
         5          5 13-NOV-02 30-NOV-02        100
         6          6 25-NOV-02 30-NOV-02        100

Your query looks for rooms available between 10-11-02 and 20-11-02

Looking at the data above that should bring back two rooms; 1 and 6. My query brings back two rooms; 1 and 6.

BOOKING_ID ROOM_ID STARTDATE ENDDATE

---------- ---------- --------- ---------
         1          1 01-NOV-02 05-NOV-02
         6          6 25-NOV-02 30-NOV-02

That leaves:

BOOKING_ID ROOM_ID STARTDATE ENDDATE DURATION

---------- ---------- --------- --------- ----------
         2          2 01-NOV-02 17-NOV-03        100
         3          3 01-NOV-02 30-NOV-02        100
         4          4 13-NOV-02 17-NOV-02        100
         5          5 13-NOV-02 30-NOV-02        100

All of which intersect between 10-11-02 and 20-11-02 and are therefore NOT available.

If anyone wants to try it, the whole data is below.

>>I'm curious to ask whether you think the theory behind my query works
>>or not, given test data that reasonable constraints would allow.
>
>Run the test and you will know. If it finds the right bookings your theory
>is correct, if it doesn't it's false.

Then it's correct. I'm not trying to get your back up Jan, and I appreciate the help youre giving me, but why is my query wrong? I don't mind being wrong, infact i'm getting used to it with SQL but i'm curious if you can give me an example that wont work.

create table booking(
booking_id number(4) primary key,
room_id number(20),
startdate date,
enddate date,
duration number(30) -- ignore
);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (1,1,to_date('01-11-02','DD-MM-YY' ),to_date('05-11-02','DD-MM-YY'),100);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (2,2,to_date('01-11-02','DD-MM-YY' ),to_date('17-11-03','DD-MM-YY'),100);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (3,3,to_date('01-11-02','DD-MM-YY' ),to_date('30-11-02','DD-MM-YY'),100);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (4,4,to_date('13-11-02','DD-MM-YY' ),to_date('17-11-02','DD-MM-YY'),100);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (5,5,to_date('13-11-02','DD-MM-YY' ),to_date('30-11-02','DD-MM-YY'),100);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (6,6,to_date('25-11-02','DD-MM-YY' ),to_date('30-11-02','DD-MM-YY'),100);

select booking_id,room_id,startdate,enddate from booking
where
to_date('10-11-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy')< to_date(startdate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy') and
to_date('20-11-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy')< to_date(startdate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy') or
to_date('10-11-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy')> to_date(enddate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy') and
to_date('20-11-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy')> to_date(enddate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy'); Received on Mon Nov 11 2002 - 08:53:43 CET