Posted by SQLChallenge on 11/14/05 21:56

This is a good one. Please poke some holes in what I came up with.

How about this though?? Let me know how you make out. The two dates I
am "plugging" would be the beginning and ending date of the period.

create table room (
RoomNumber varchar(5),
StartDate datetime,
EndDate datetime
)

insert into room values('123','2005-10-04','2005-10-05')
insert into room values('123','2005-10-06','2005-10-07')
insert into room values('123','2005-10-09','2005-10-10')

select distinct roomnumber
from room r1
where startdate not between '2005-10-08' and '2005-10-09'
and enddate not between '2005-10-08' and '2005-10-09'
and not exists(select * from room r2
where r2.roomnumber = r1.roomnumber
and startdate between '2005-10-08' and
'2005-10-09'
and enddate between '2005-10-08' and '2005-10-09')

---
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
---

• Next in forum: [ANNOUNCE] Stylus Studio 2006 XML Enterprise Edition Now Available
• Prev in forum: Re: Need security advice on xp_cmdshell, bcp, xml procedure
• Thread view: Re: Room availability

[Reply to this message]