Comparing two date periods for overlapping

hi guys,

i have a booking table which has the following columns...

booking
--------------
dCheckin (format 11/9/2006 12:00:00 AM)
dCheckout (format 11/11/2006 12:00:00 AM)

when a new booking is entered, we want to make sure that the period entered does not conflict with an existing record.

not sure how to go about building the query required. any help would be greatly appreciated.

mikeDo periods that "touch" count as an overlap? Do you need to consider rooms, customers, or anything else for an overlap, or is all of the data in the table the same?

-PatP|||it does matter if they are touching eg. someone cannot checkin during a period already occupied. the other data is in another table.

m.|||ahh sorry patp. i see what you mean. touching yes it does matter. a person cannot checkin on the day someone checks out.

mike|||CREATE TABLE #patp (
id INT IDENTITY
, dCheckin DATETIME
, dCheckout DATETIME
)

INSERT INTO #patp (
dCheckin, dCheckout
) SELECT '2006-01-01', '2006-01-10'
UNION ALL SELECT '2006-01-15', '2006-01-20'
UNION ALL SELECT '2006-02-01', '2006-02-10'
UNION ALL SELECT '2006-02-10', '2006-02-15'
UNION ALL SELECT '2006-03-01', '2006-03-10'
UNION ALL SELECT '2006-03-02', '2006-03-07'
UNION ALL SELECT '2006-04-01', '2006-04-10'
UNION ALL SELECT '2006-04-08', '2006-04-13'

SELECT *
FROM #patp AS a
WHERE EXISTS (SELECT *
FROM #patp AS b
WHERE b.id != a.id -- Never compare a row with itself
AND (b.dCheckin <= a.dCheckout -- A starts before B ends
AND a.dCheckin <= b.dCheckout)) -- B ends after A starts
ORDER BY a.dCheckin

DROP TABLE #patp-PatP