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

Compare BIGINT ranges to check for overlapping

I have a table that has two BIGINT columns. The first column is for storing the beginning of a range and the second column is for storing the end of the range.

I'm trying to find a way to check to see if the BIGINT values of an INSERT statement are present within existing BIGINT ranges. If so, I want the insert statement to fail.

A simplified example of this would be this:

EXISTING DATA
col1 col2
100000 110000

NEW DATA
col1 col2
101000 101999

I don't want the new data to be inserted b/c at least one of the values contained in its range is already present in the existing data.

I've seen ways to do this with date ranges, but haven't figured out how to accomplish this with BIGINTs.

Any help would be appreciated.

Thanks,

AndrewIf you have something (a constraint or trigger) in place to make sure that col1 < col2, then you can easily check to see if there is an existing row for which both existing.col1 and new.col1 are less than both existing.col2 and new.col2

If you picture this, you will see why. If one of the col2s shuts off a range before the other col1 starts, then you don't have an overlap.

Now, since we already know that:

existing.col1 <= existing.col2
and
new.col1 <= new.col2

All we need to do to check is:

where existing.col1 <= new.col2
and new.col1 <= existing.col2

So, you want something like a trigger which does:

if exists (
select *
from inserted i
where exists (select * from myTable e where e.col1 <= i.col2 and i.col1 <= e.col2)
)
rollback transaction

Now, you will want to make sure you have a trigger on myTable which covers both col1 and col2, because you want this to run quickly. But it should work. :)

Rob|||And by the way - <= and >= will work just fine with bigints... :)|||Rob,

Thank you very much for your answer. Problem solved!

Andrew