Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newsfeed-east.nntpserver.com!nntpserver.com!eusc.inter.net!nautilus.eusc.inter.net!news.csl-gmbh.net!news1.dtag.de!cer.yubc.net!etf.bg.ac.yu!not-for-mail From: "Damjan S. Vujnovic" Newsgroups: comp.databases.theory Subject: Re: Hotel Booking dates subquery Date: Mon, 11 Nov 2002 17:19:54 -0800 Organization: ETF news server Lines: 105 Message-ID: References: <5dkosuog0v9698ei9juhfbm2pull0nqqn2@4ax.com> <3dcecc70$1@news.uia.ac.be> <3dcf0255$1@news.uia.ac.be> NNTP-Posting-Host: galeb.etf.bg.ac.yu X-Trace: news.etf.bg.ac.yu 1037031549 18715 147.91.8.64 (11 Nov 2002 16:19:09 GMT) X-Complaints-To: abuse@etf.bg.ac.yu NNTP-Posting-Date: Mon, 11 Nov 2002 16:19:09 +0000 (UTC) X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Xref: newsfeed1.easynews.com comp.databases.theory:23555 X-Received-Date: Mon, 11 Nov 2002 10:19:52 MST (news.easynews.com) First of all, your query should return only room_id's, so I'll assume that your query looks somewhat like (and I'll reference it as 'your query' in this post): select room_id 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'); Second, AFAI understand, you are looking for free rooms between two given dates. Let's suppose that the table BOOKING is empty (no bookings at all). For any two given dates, your query will say that there were no free rooms (but all rooms were free). Third, try the following (the only difference to your example is when inserting touple booking_id = 4, you set room_id=1): insert into room(room_id) values (1); insert into room(room_id) values (2); insert into room(room_id) values (3); insert into room(room_id) values (4); insert into room(room_id) values (5); insert into room(room_id) values (6); 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,1,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); /* ROOM_ID ---------- 1 2 3 4 5 6 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 1 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 */ select room_id 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'); Your query will say (again) that rooms 1 and 6 were free, but room 1 wasn't it was occupied between 13-NOV-02 and 17-NOV-02!