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
