May 6, 2008 at 6:13 am
I have a table called ReservationRooms which contains these columns: RESNO, ROOMNO, CHECKINDATE and CHECKOUTDATE. I need to write a SELECT query to return ONLY those reservations where a room may have been booked more than once. I don't know how to write the TSQL to check whether any date in the range between CHECKINDATE and (CHECKOUTDATE - 1) coincides with any other date range on any other reservation on the same room no.
Can anyone please help?
May 6, 2008 at 12:25 pm
--sample data
create table reservations
(
RESNO int
,ROOMNO int
,CHECKINDATE datetime
, CHECKOUTDATE datetime
)
insert into reservations
select 1,1,'20080101','20080201'
insert into reservations
select 2,1,'20080301','20080401'
insert into reservations
select 3,1,'20080115','20080116'
insert into reservations
select 4,1,'20071201','20091201'
--gives conflicts
--res1= usually minimum reservation
--res2= usuallymaximum reservation time
select res1.*,res2.*
from reservations res1
inner join reservations res2
on
/*checkindate between res2.checkindate*/
(
(res1.CHECKINDATE >=res2.CHECKINDATE AND res1.CHECKINDATE<res2.CHECKOUTDATE AND res1.ROOMNO=res2.roomno)
/*checkoutdate between res2.checkindate and res2.checkoutdate */
or (res1.CHECKOUTDATE>= res2.CHECKINDATE and res1.CHECKOUTDATE<res2.CHECKOUTDATE AND res1.ROOMNO=res2.roomno)
)
and not (res1.RESNO=res2.RESNO)
order by res1.ROOMNO,res1.RESNO
I migth have forgotten a condition
May 6, 2008 at 12:41 pm
It's pretty simple.
What you need are reservations that have a check-in date before the other reservation's check-out date, and a check-out date after the other reservation's check-in date.
select res1.resno, res1.roomno
from dbo.reservationrooms res1
inner join dbo.reservationrooms res2
on res1.roomno = res2.roomno
and res1.checkoutdate => res2.checkindate
and res1.checkindate <= res2.checkoutdate
and res1.resno != res2.resno
Try that, see if it does what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 7, 2008 at 2:00 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply