Mark-101232 (6/15/2011)
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;
I realize there was a follow up but, just to be sure... This particular one doesn't work quite right. Add the following data to the test table and see...
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/1900', '1/1/1900')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/2/1900', '1/2/1900')
INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/3/1900', '1/3/1900')
--Jeff Moden
Change is inevitable... Change for the better is not.