Detecting Overlapping Date Ranges

I have a table with two date/time cols:

Date1, Date2
1/1/2002, 6/1/2002
6/2/2002, 8/30/2002
8/31/2002, 12/15/2002
12/01/2002, 4/15/2003
:
:
:


I need some way to check this table to return any rows where dates overlap.

Thus,

1/1/2002, 6/1/2002
6/2/2002, 8/30/2002

... are OK




But,

8/31/2002, 12/15/2002
12/01/2002, 4/15/2003

... is NOT ok, because the second row date-range overlaps the first date-range


Either TSql or client side script will work for my purposes, but I'd prefer TSQL or SP approach.

Any help appreciated.

Kirby

You can use a self-join to match or compare different rows from the same table. This query assumes you have a table Tbl with a primary key on a column called Key.

SELECT t1.date1, t1.date2, t2.date1, t2.date2
FROM Tbl t1
INNER JOIN Tbl t2
ON (t1.Key <> t2.Key) AND (t1.date1 < t2.date1 AND t1.date2 >= t2.date1)

The above query does not check if the end date of the second period is after the end date of the first period. If that is also a requirement, then use this:

SELECT t1.date1, t1.date2, t2.date1, t2.date2
FROM Tbl t1
INNER JOIN Tbl t2
ON (t1.Key <> t2.Key) AND (t1.date1 < t2.date1 AND t1.date2 >= t2.date1 AND t1.date2 <= t2.date2)

You can also use this syntax:

SELECT ........
FROM Tbl t1, Tbl t2
WHERE ...........

... but it is preferred to use the JOIN syntax.

A self join. I should have thought of that. But self joins seem confusing to me. I can't imagine why, though. Two copies of the same table are two tables as much as two different tables are.

Thanks.

Kirby