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
---