Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newsfeed.news2me.com!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.icl.net!newsfeed.fjserv.net!diablo.theplanet.net!news.theplanet.net!not-for-mail From: Scotty Newsgroups: comp.databases.theory Subject: Re: Hotel Booking dates subquery Date: Sun, 10 Nov 2002 23:34:11 +0000 Lines: 27 Message-ID: <5hqtsugqkln657gquou0hi9depojiht07f@4ax.com> References: <5dkosuog0v9698ei9juhfbm2pull0nqqn2@4ax.com> <3ocrsuo3g5deskmoorvd50qs6sfovuv0j7@4ax.com> <0sgrsuctlseepau8joqfrtjvddck2iu6jn@4ax.com> <99irsusn3hl706ov9saqp4n63uaublvvsg@4ax.com> <2kmssu0oq18l60fc1srtbf6rp7e3euobve@4ax.com> NNTP-Posting-Host: 81.77.121.64 Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: news5.svr.pol.co.uk 1036971245 24486 81.77.121.64 (10 Nov 2002 23:34:05 GMT) NNTP-Posting-Date: 10 Nov 2002 23:34:05 GMT X-Complaints-To: abuse@theplanet.net Summary: YHBT X-Newsreader: Forte Agent 1.8/32.548 X-No-Archive: yes Xref: newsfeed1.easynews.com comp.databases.theory:23508 X-Received-Date: Sun, 10 Nov 2002 16:33:50 MST (news.easynews.com) Damjan S. Vujnovic wrote: >Here's the "template" query. For additional explanation see Jan's post. >It works correct for dates in the past time, and in order to make it >correct for dates in future time you should change either the query >or the model you adopted (the way you represent 'eternity' end_date). > >SELECT room_id >FROM rooms >WHERE room_id NOT IN ( > SELECT DISTINCT room_id > FROM bookings > WHERE room_status='occupied' and > (('29-11-02'>start_date and '29-11-02' ('05-12-02'>start_date and '05-12-02' ('29-11-02'end_date)) >) Damjan thanks! Not sure what you mean by "the way you represent 'eternity' end_date" but i'll do some reading and see if the bulb lights up. Cheers BTW, where do these "Template" queries come from? Do their authors ever get remebered - Fibonacci and Mr Bubble Sort et al.