Identifying a gap in member month data based on service date

Question

There's two data sets:

Data set 1:

Member ID	MIN_SRVC_DT	NEW OR CURRENT
111	202104	NEW
222	201801	NEW
333	202002	NEW

Data set 2:

Member ID	COVERAGE_MONTH
111	202102
111	202103
111	202104
111	202105
111	202106
222	202101
222	202102
222	202103
222	202104
222	202105
333	201909
333	201910
333	202101
333	202102
333	202103
333	202104

For each member, if they are tagged as "NEW" in dataset 1, but do not have two consecutive member months in dataset 2 PRIOR to the min_srvc_dt in dataset 1, then flip to "CURRENT".

The ending result table should look like this:

Data set 1:

Member ID	MIN_SRVC_DT	NEW OR CURRENT
111	202104	NEW
222	201801	CURRENT
333	202105	CURRENT

Member 111 stays NEW because they have coverage months 202102 and 202103 in Dataset 2. Member 222 switches to current because the earliest coverage month they have starts in 202101 but their min_srvc_dt is 201801 so they needed a coverage month 2 months prior to 201801 (i.e. 201712 and 201711). Member 333 changes to current because there is a gap in coverage (they have coverage 201909 and 201910 and then in 2021, but they needed coverage in 202001 and 201912)

How can I code this in SQL Server? How can I find gaps in coverage in dataset 2 using min_srvc_dt in data set 1?

I tried this already:

select memberID, coverage_month
    , previous_month = lag(coverage_month) over (partition by memberID order by memberID, coverage_month)
into #temp
from #database2

select *
from #dataset1
where memberID not in (
    select distinct memberID
    from #dataset2
    where min_srvc_dt between previous_month and coverage_month
) 

But this only gave what I was looking for member 222.

Then I tried this to try to identify gaps but it didn't really give me what I was looking for...

select memberID, cvg_month, previous_month, cvg_month-previous_month as Month_Gap
from #temp 
where CVG_MONTH-previous_month > 0 and CVG_MONTH-previous_month >= 1

Answer 1

based on your sample data, you can use this query to update your table, am using COVERAGE_MONTH as an int you can use them as dates.

SELECT *
FROM #TEMP1

UPDATE #TEMP1
SET NEW_OR_CURRENT = 'CURRENT'
WHERE Member_ID IN (
        SELECT Member_ID
        FROM (
            SELECT t2.*
                ,ISNULL(LAG(COVERAGE_MONTH) OVER (
                        PARTITION BY t2.Member_ID ORDER BY COVERAGE_MONTH
                        ), MIN_SRVC_DT) AS previous_COVERAGE_MONTH
                ,COVERAGE_MONTH - ISNULL(LAG(COVERAGE_MONTH) OVER (
                        PARTITION BY t2.Member_ID ORDER BY COVERAGE_MONTH
                        ), MIN_SRVC_DT) AS diff
            FROM #TEMP2 t2
            JOIN #TEMP1 t1 ON t2.Member_ID = t1.Member_ID
            ) a
        WHERE diff > 1
        )

SELECT *
FROM #TEMP1