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

Comparing Date ranges

I have two sets of dates to work with. One is an existing booking with a start and an end date. The other is a new booking with a start and an end date. I want to compare them and calculate how much overlap there is. If the overlap is over a certain amount (say 4 days), then I want to flag the user.

Is there any thing I can use in terms of a SQL query to assist in this comparison? I'm relatively new to SQL so I'm not entirely sure what functions and keywords are available to me to make this comparison.BOL (Books On Line) is the best source for some date functions..they also have sample codes..

hth|||You can you the DATEDIFF(datepart, datetime1,datetime2) function to get the difference between two dates.
Take a look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_2c1f.asp
for SQL Date Function.|||I actually ended up checking whether the start and end dates of each set were with in a certain range using a BETWEEN X AND Y syntax.