Try this

WITH Starts AS (

SELECT a.StationID,a.dtmStart,

ROW_NUMBER() OVER(PARTITION BY a.StationID ORDER BY a.dtmStart) AS rn

FROM @Events a

WHERE NOT EXISTS(SELECT * FROM @Events b

WHERE b.StationID = a.StationID

AND b.dtmEnd = a.dtmStart)),

Ends AS (

SELECT a.StationID,a.dtmEnd,

ROW_NUMBER() OVER(PARTITION BY a.StationID ORDER BY a.dtmEnd) AS rn

FROM @Events a

WHERE NOT EXISTS(SELECT * FROM @Events b

WHERE b.StationID=a.StationID

AND b.dtmStart = a.dtmEnd))

SELECT s.StationID,s.dtmStart,e.dtmEnd

FROM Starts s

INNER JOIN Ends e ON e.StationID=s.StationID

AND e.rn=s.rn

INNER JOIN @Events t ON t.StationID=s.StationID

AND t.dtmStart BETWEEN s.dtmStart AND e.dtmEnd

GROUP BY s.StationID,s.dtmStart,e.dtmEnd

HAVING COUNT(*) > 1

ORDER BY s.StationID,s.dtmStart,e.dtmEnd;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537