Finding efficient overlapped entries in a SQL table

Posted by Laoneo on Stack Overflow See other posts from Stack Overflow or by Laoneo
Published on 2010-06-02T11:48:39Z Indexed on 2010/06/02 12:33 UTC
Read the original article Hit count: 239

Filed under:
|
|

What is the most efficient way to find all entries which do overlap with others in the same table? Every entry has a start and end date. For example I have the following database setup:

CREATE TABLE DEMO
(
    DEMO_ID  int  IDENTITY ,
    START date  NOT NULL ,
    END  date  NOT NULL
};

INSERT INTO DEMO (DEMO_ID, START, END) VALUES (1, '20100201', '20100205');
INSERT INTO DEMO (DEMO_ID, START, END) VALUES (2, '20100202', '20100204');
INSERT INTO DEMO (DEMO_ID, START, END) VALUES (3, '20100204', '20100208');
INSERT INTO DEMO (DEMO_ID, START, END) VALUES (4, '20100206', '20100211');

My query looks as follow:

SELECT DISTINCT * 
FROM DEMO A, DEMO B
WHERE A.DEMO_ID != B.DEMO_ID
AND A.START < B.END
AND B.START < A.END

The problem is when my demo table has for example 20'000 rows the query takes too long. My environment is MS SQL Server 2008. Thanks for any more efficient solution

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server