Another way

WITH Starts AS (

SELECT a.clientID, a.lastName, a.firstName, a.startDate

FROM #temp a

WHERE NOT EXISTS (SELECT * FROM #temp b

WHERE b.clientID=a.clientID

AND b.endDate=a.startDate)),

Ends AS (

SELECT a.clientID, a.endDate

FROM #temp a

WHERE NOT EXISTS (SELECT * FROM #temp b

WHERE b.clientID=a.clientID

AND b.startDate=a.endDate))

SELECT s.clientID, s.lastName, s.firstName, s.startDate,

MIN(e.endDate) AS endDate,

DATEDIFF(day,s.startDate,MIN(e.endDate)) AS dtDiff

FROM Starts s

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

AND s.startDate<e.endDate

GROUP BY s.clientID, s.lastName, s.firstName, s.startDate

ORDER BY s.clientID, s.startDate;

____________________________________________________

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