This should work for you, not particularly efficient though

WITH Sdates AS (

SELECT a.Sdate,a.SRC,a.OrgNo

FROM TEST a

WHERE NOT EXISTS(SELECT * FROM TEST b

WHERE a.SRC=b.SRC

AND a.OrgNo=b.OrgNo

AND a.Sdate IN (b.Edate,DATEADD(day,1,b.Edate)))),

Edates AS (

SELECT a.Edate,a.SRC,a.OrgNo

FROM TEST a

WHERE NOT EXISTS(SELECT * FROM TEST b

WHERE a.SRC=b.SRC

AND a.OrgNo=b.OrgNo

AND a.Edate IN (b.Sdate,DATEADD(day,-1,b.Sdate))))

SELECT s.SRC,s.OrgNo,s.Sdate,

MIN(e.Edate) AS Edate

FROM Sdates s

INNER JOIN Edates e ON e.SRC=s.SRC

AND e.OrgNo=s.OrgNo

AND e.Edate>=s.Sdate

GROUP BY s.Sdate,s.SRC,s.OrgNo;

____________________________________________________

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