Jeff Moden (6/18/2011)

---

Actually, I was able to break the Mark's second rendition...

CREATE TABLE TEST( SRC varchar(6)not null,

OrgNo varchar(5)not null,

Sdate datetime Not nUll,

Edate datetime Not Null)

INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/1999','12/31/1999')

INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2011','6/30/2012')

INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2012', '12/31/2012')

INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2016', '6/1/2017')

INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','6/2/2017', '12/31/2018')

INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/2019', '12/31/2020')

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')

INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/5/1900', '1/5/1900')

INSERT INTO TEST (SRC ,OrgNo ,Sdate ,Edate ) Values ('abc','99999','1/1/1901', '12/31/1901')

DECLARE @holdTable TABLE

(Rowid int IDENTITY(1,1) Not null ,

SRC varchar(6)not null,

OrgNo varchar(5)not null,

Sdate datetime Not nUll,

Edate datetime Not Null)

---insert data into temp table

INSERT INTO @holdTable (SRC ,OrgNo ,Sdate ,Edate )

SELECT SRC ,OrgNo,Sdate,Edate from Test WHERE OrgNo = '99999' AND SRC = 'abc'

;

; with diffenrentiator as

(

SELECT s.Rowid sRowID, s.SRC sSRC , s.OrgNo sOrgNo , s.Sdate sSate

,s.Edate sEdate , t.*,

case when DATEDIFF(dd, s.edate , t.Sdate) <> 1

then 1

else 0

end indicator

FROM @holdTable s

LEFT JOIN @holdTable t

ON t.Rowid = s.Rowid +1

AND s.Orgno = t.Orgno

AND s.SRC = t.SRC

)

--select * from diffenrentiator

,

sequenced As

(

select sRowid , sSRC, sORgNo , sSate,

coalesce( Edate ,sEdate ) Edate,

rndiff = srowid - ROW_NUMBER() over(partition by sSRC , sORgNo , indicator order by sRowid)

from diffenrentiator

where indicator <> 1

)

--select * from sequenced

select sSRC, sORgNo , MIN( sSate) StartDate , MAX(Edate) EndDate

from sequenced

GROUP BY

sSRC, sORgNo , rndiff

Notice that the above code only returns 3 ranges. Here's what it returned...

sSRCsORgNoStartDateEndDate

abc999992016-01-01 00:00:00.0002020-12-31 00:00:00.000

abc999991900-01-01 00:00:00.0001900-01-03 00:00:00.000

abc999991901-01-01 00:00:00.0001901-12-31 00:00:00.000

Here's what it should have returned...

SrcOrgNoSDateEDate

abc999991900-01-01 00:00:00.0001900-01-03 00:00:00.000

abc999991900-01-05 00:00:00.0001900-01-05 00:00:00.000

abc999991901-01-01 00:00:00.0001901-12-31 00:00:00.000

abc999991999-01-01 00:00:00.0001999-12-31 00:00:00.000

abc999992011-01-01 00:00:00.0002012-12-31 00:00:00.000

abc999992016-01-01 00:00:00.0002020-12-31 00:00:00.000