CREATE TABLE #SourceData
(
	--RID UNIQUEIDENTIFIER,
	CI NVARCHAR(10) NOT NULL,
	DateTimeStarted DATE NOT NULL,
	DateTimeCompleted DATE NOT NULL
)

INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20190110' AS DATE),
CAST('20190114' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20190112' AS DATE),
CAST('20190117' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20190116' AS DATE),
CAST('20190120' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20181003' AS DATE),
CAST('20181004' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20181003' AS DATE),
CAST('20181005' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20181007' AS DATE),
CAST('20181009' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20181008' AS DATE),
CAST('20191010' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20171010' AS DATE),
CAST('20171014' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20170501' AS DATE),
CAST('20170503' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20170502' AS DATE),
CAST('20170506' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20170505' AS DATE),
CAST('20170520' AS DATE)
)

;WITH Src AS
(
	SELECT CI, caMin.MinStarted, caMax.MaxCompleted
	FROM #SourceData sd
	CROSS APPLY
	(
		SELECT MIN(sdm.DateTimeStarted) AS MinStarted
		FROM #SourceData sdm
		WHERE sdm.CI = sd.CI
		AND sdm.DateTimeStarted <= sd.DateTimeCompleted
		AND sdm.DateTimeCompleted >= sd.DateTimeStarted 
	) caMin
	CROSS APPLY
	(
		SELECT MAX(sdma.DateTimeCompleted) AS MaxCompleted
		FROM #SourceData sdma
		WHERE sdma.CI = sd.CI
		AND sdma.DateTimeStarted <= sd.DateTimeCompleted
		AND sdma.DateTimeCompleted >= sd.DateTimeStarted 
	) caMax
)
SELECT * FROM Src
GROUP BY CI,MinStarted, MaxCompleted

with
src1 as
	(select *,
		case when lag(DateTimeCompleted) over (partition by CI order by DateTimeStarted, DateTimeCompleted) >= DateTimeStarted then 0 else 1 end isOpen,
		case when lead(DateTimeStarted) over (partition by CI order by DateTimeStarted, DateTimeCompleted) <= DateTimeCompleted then 0 else 1 end isClose
	from #SourceData),
src2 as
	(select *,
		case when isClose = 1 then DateTimeCompleted else lead(DateTimeCompleted) over (partition by CI order by DateTimeStarted, DateTimeCompleted) end closeDate
	from src1
	where isOpen = 1 or isClose = 1)
select CI, DateTimeStarted, closeDate as DateTimeCompleted
from src2
where isOpen = 1

dim xm(0 to 50000) as long
set rst=currentdb.openrecordset("select dnach,dkon from tab") 
do while  rst.eof=false
for d1=dnach to dkon
xm(d1)=1
next d1
rst.movenext
k=0  
for d1=1 to 50000
if xm(d1)=0 then
if k>0 then
  debug.print dn,dk
  dn=d1
  dk=d1
  k=0
endif
else
  k=k+1
  dk=d1
endif
next d1

with t1 as
(
select
 *,
 dense_rank() over (partition by t.CI order by t.DateTimeStarted) as r
from
 #SourceData t
where
 not exists(select 1 from #SourceData where CI = t.CI and DateTimeStarted < t.DateTimeStarted and DateTimeCompleted > t.DateTimeStarted)
),
t2 as
(
select
 *,
 dense_rank() over (partition by t.CI order by t.DateTimeStarted) as r
from
 #SourceData t
where
 not exists(select 1 from #SourceData where CI = t.CI and DateTimeCompleted > t.DateTimeCompleted and DateTimeStarted < t.DateTimeCompleted)
)
select
 t1.CI, min(t1.DateTimeStarted), max(t2.DateTimeCompleted)
from
 t1 join
 t2 on t2.CI = t1.CI and t2.r = t1.r
group by
 t1.CI, t1.r;

declare @b table(CI NVARCHAR(10) NOT NULL, DateTimeStarted DATE NOT NULL, n int identity,  primary key(CI , n));
declare @e table(CI NVARCHAR(10) NOT NULL, DateTimeCompleted DATE NOT NULL, n int identity, primary key(CI , n));

with t as ( select *  from @SourceData)
insert @b
  select CI, DateTimeStarted from t where not exists( select * from t as t0 where t0.CI = t.CI and ( t0.DateTimeStarted < t.DateTimeStarted and t.DateTimeStarted <= t0.DateTimeCompleted and t0.n <> t.n ) or (t0.DateTimeStarted = t.DateTimeStarted and t0.n < t.n ) ) order by CI, DateTimeStarted asc

select * from @b;

with t as ( select *  from @SourceData)
insert @e
  select CI, DateTimeStarted from t where not exists( select * from t as t0 where t0.CI = t.CI and ( t0.DateTimeStarted <= t.DateTimeCompleted and t.DateTimeCompleted < t0.DateTimeCompleted and t0.n <> t.n ) or ( t.DateTimeCompleted = t0.DateTimeCompleted and t0.n > t.n ) ) order by CI, DateTimeCompleted asc

select * from @e;

select b.DateTimeStarted, e.DateTimeCompleted
  from @b as b inner join @e as e on e.CI = b.CI and e.n = b.n

Sub mm190426()
Dim xm(0 To 50000) As Long
Dim d1 As Long, j1 As Long, r1 As Long, r1k As Long
Dim dnach As Long, dkon As Long, k, dn, dk
r1 = 1
r1k = 11
For j1 = r1 To r1k
dnach = Cells(j1, 1)
dkon = Cells(j1, 2)
Debug.Print j1, Cells(j1, 1), Cells(j1, 2), Cells(j1, 1) - Cells(j1, 2)
    For d1 = dnach To dkon
    xm(d1) = 1
    Next d1
Next j1

''''''''''''
k = 0
For d1 = 1 To 50000
    If xm(d1) = 0 Then
        If k > 0 Then
        Debug.Print CDate(dn), CDate(dk), dk - dn + 1
        End If
         k = 0
    Else
      k = k + 1
      If k = 1 Then dn = d1
      dk = d1
    End If
Next d1

End Sub

with
t1 as
	(select CI, DateTimeStarted, dense_rank() over (partition by t.CI order by t.DateTimeStarted) as r
	from #SourceData t
	where not exists(select 1 from #SourceData where CI = t.CI and DateTimeStarted < t.DateTimeStarted and DateTimeCompleted > t.DateTimeStarted)),
t2 as
	(select CI, DateTimeCompleted, dense_rank() over (partition by t.CI order by t.DateTimeCompleted) as r
	from #SourceData t
	where not exists(select 1 from #SourceData where CI = t.CI and DateTimeStarted < t.DateTimeCompleted and DateTimeCompleted > t.DateTimeCompleted))
select distinct
	t1.CI, t1.DateTimeStarted, t2.DateTimeCompleted
from t1
inner join t2 on t2.CI = t1.CI and t2.r = t1.r