checking gap in data with start and end date in SQL Server

--- Create table to hold data and show that logic works


Create Table #TempDateRange
(ID int
, Start_Date Date
, End_Date Date
)

Insert into #TempDateRange
(ID, Start_Date, End_Date)
SELECT 1, '2013-01-01', '2013-09-30'
UNION
SELECT 1, '2013-10-01', '2013-12-31'
UNION
SELECT 1, '2014-01-01', '2014-12-31'
UNION
SELECT 2,'2013-01-01', '2013-08-31'
UNION
SELECT 2,'2013-10-01', '2013-12-31'
UNION
SELECT 2, '2014-01-01', '2014-12-31'


----- Logic to club the Eligibility Record into one if there is a Continuous Enrollment for the Member

SELECT
       s1.ID
    ,  s1.Start_Date
, MIN(t1.End_Date) AS End_Date

INTO #Consolidated_Data
FROM #TempDateRange s1
INNER JOIN #TempDateRange t1
ON t1.ID = s1.ID
AND s1.Start_Date < = t1.End_Date
AND NOT EXISTS
(
SELECT *
FROM #TempDateRange t2
WHERE t2.ID = t1.ID
AND DATEADD(DAY, 1, t1.End_Date) > = t2.Start_Date
AND t1.End_Date < t2.End_Date
)

  WHERE NOT EXISTS
     (
   SELECT *
   FROM #TempDateRange s2
   WHERE s2.ID = s1.ID
   AND s1.Start_Date > s2.Start_Date
   AND DATEADD(DAY, - 1, s1.Start_Date) < = s2.End_Date
 )

GROUP BY s1.ID , s1.Start_Date
ORDER BY s1.ID , s1.Start_Date;


-- Ranking Based on Dates to Calculate GAP Calculation

SELECT
   ID,
Start_Date,
End_Date,
RANK() OVER ( PARTITION BY ID   ORDER BY Start_Date ASC,  End_Date ASC   ) RN,
RANK() OVER ( PARTITION BY ID   ORDER BY Start_Date DESC, End_Date DESC ) RN_Inverse,
' ' AS GAP
INTO #Consolidated_Data_Rank
FROM #Consolidated_Data;

Select * FROM #Consolidated_Data
Select * FROM #Consolidated_Data_Rank