Complicated date query

[katieb]

Hi,

We have a database that books rooms, and we need a query that can determine whether or not any bookings are double bookings. The main problem is the searching for bookings that occur during the time of another meeting. What can we do to bring up a list of bookings based on StartTime and EndTime in the format dd/mm/yy hh:mm:ss and the RoomID to bring back a list of double bookings.

Any help would be much appreciated as this is driving me mad!

Thanks

Katie

 

[clapag22]

I guess the first question would be is what is the PKID on the table? If it is a roomid/starttime combo it's difficult. If it's another field (or fields), it's easy.

--With no other primary key:

create table yourtable (roomid int, starttime datetime, endtime datetime)

insert into yourtable values (1, '05/05/2004 12:00PM', '05/05/2004 2:00PM')
insert into yourtable values (2, '05/05/2004 1:00PM', '05/05/2004 2:00PM')
insert into yourtable values (3, '05/05/2004 10:00AM', '05/05/2004 12:00PM')
insert into yourtable values (1, '05/05/2004 12:30PM', '05/05/2004 1:00PM')

select *
from yourtable t1, yourtable t2
where t1.roomid = t2.roomid
and (t1.starttime between t2.starttime and t2.endtime
	or t1.endtime between t2.starttime and t2.endtime)
--this returns everything

--With a different primary key:

alter table yourtable add pkid int not null identity(1,1)

select *
from yourtable t1, yourtable t2
where t1.roomid = t2.roomid
and t1.pkid != t2.pkid
and (t1.starttime between t2.starttime and t2.endtime
	or t1.endtime between t2.starttime and t2.endtime)

--Will work fine

W/O having the separate PK I can't see making this work without a ton of coding. But someone else here may very well be able to. My suggestion would be to add the PKID field like I did above in the code if needs be and go that route.