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
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