Unfortunately, ColdCoffee's code breaks.

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