Using the Calendar table I posted in one of the previous posts above, here's what the code at the link that Lutz posted should look like with a couple of very necessary corrections to make it work for this thread and just about any place else where overlapping date ranges need to be solved...

WITH

cteExplodeDateRanges AS

(

SELECT DISTINCT Src, OrgNo,

ExplodedDate = c.Dt

FROM dbo.Test test

CROSS JOIN dbo.Calendar c

WHERE c.Dt BETWEEN test.SDate AND test.EDate

)

,

cteGroupDates AS

(

SELECT Src, OrgNo, ExplodedDate,

DateGroup = ExplodedDate - ROW_NUMBER() OVER (PARTITION BY Src, OrgNo ORDER BY ExplodedDate)

FROM cteExplodeDateRanges

)

SELECT Src, OrgNo, SDate = MIN(ExplodedDate), EDate = MAX(ExplodedDate)

FROM cteGroupDates

GROUP BY Src, OrgNo, DateGroup

ORDER BY Src, OrgNo, DateGroup

;

Using the previous 11 rows already posted several times, it produces the correct answer.

If you want to have some fun with testing for performance, you can run the following code to setup a good number more rows...

WITH

cteGenRandomDates AS

(

SELECT TOP (10000)

SDate = DATEADD(dd,ABS(CHECKSUM(NEWID()))%DATEDIFF(dd,'2000','2050'),CAST('2000' AS DATETIME))

FROM sys.all_columns ac1,

sys.all_columns ac2

)

SELECT Src = 'abc',

OrgNo = '99999',

SDate,

EDate = DATEADD(dd,ABS(CHECKSUM(NEWID()))%15,SDate)

INTO dbo.Test

FROM cteGenRandomDates

;

Still, the corrected code above still takes a whopping 1.2 to 1.5 seconds to return the answer on a lousy 10,000 rows. I think I may know a better way and I'll work on it over the next day or two.