Overlapping segment for same ID

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Overlapping segment for same ID

Author

Topic

sqlserver_newbee
Starting Member

11 Posts

Posted - 2008-10-14 : 13:56:03

ID Start_Date End_Date PRoll
--------------------------------------------------------------------------
1 02-03-2007 02-03-2008 1A
1 03-31-2008 12-12-2008 1C
2 01-01-2008 04-25-2008 2A
2 03-03-2008 10-21-2008 2B
3 05-06-2007 08-01-2007 3A
3 08-08-2007 08-08-2008 3B

I have a set like above where each id has multiple segments. I want to get all the ids where the segments are overlapping. For example ID 2 where End_Date on first segments is > Start Date of second segment. The dataset is sorted on ID, Start and End Date.

I am not able to come with the best way of finding overlapping segments for each id. I appreciate all your suggestions!

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 14:03:34

[code]SELECT t1.ID
FROM Table t1
INNER JOIN Table t2
ON t2.ID=t1.ID
AND t2.Start_Date <t1.End_Date
GROUP BY t1.ID[/code]

sqlserver_newbee
Starting Member

11 Posts

Posted - 2008-10-14 : 14:22:23

quote:

Originally posted by visakh16

SELECT t1.ID
FROM Table t1
INNER JOIN Table t2 
ON t2.ID=t1.ID
AND t2.Start_Date <t1.End_Date
GROUP BY t1.ID

This doesn't get me what I want because it gives me all the records where start_dt< end_dt

So my output will be id's - 1, 2, 3.
While i just want id 2 which has the overlap, something like id.row1.end_dt> id.row2.start_dt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 15:23:14

[code]DECLARE @Sample TABLE
(
ID INT,
Start_Date DATETIME,
End_Date DATETIME,
PRoll CHAR(2)
)

SET DATEFORMAT MDY

INSERT @Sample
SELECT 1, '02-03-2007', '02-03-2008', '1A' UNION ALL
SELECT 1, '03-31-2008', '12-12-2008', '1C' UNION ALL
SELECT 2, '01-01-2008', '04-25-2008', '2A' UNION ALL
SELECT 2, '03-03-2008', '10-21-2008', '2B' UNION ALL
SELECT 3, '05-06-2007', '08-01-2007', '3A' UNION ALL
SELECT 3, '08-08-2007', '08-08-2008', '3B'

SELECT s1.ID,
s1.Start_Date,
s1.End_Date,
s1.PRoll
FROM @Sample AS s1
INNER JOIN @Sample AS s2 ON s2.ID = s1.ID
WHERE s1.Start_Date <= s2.End_Date
AND s1.End_Date >= s2.Start_Date
AND s1.Start_Date <> s2.Start_Date
AND s1.End_Date <> s2.End_Date[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources