SQL Server Performance

Detecting Overlapping Date Ranges

Discussion in 'General Developer Questions' started by KirbyWallace, Jul 12, 2006.

  1. KirbyWallace New Member


    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
  2. Adriaan New Member

    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.
  3. KirbyWallace New Member

    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

Share This Page