根据服务日期识别会员月份数据的差距

Question

有两个数据集：

数据集 1：

成员 ID	MIN_SRVC_DT	NEW OR CURRENT
111	202104	NEW
222	201801	NEW
333	202002	NEW

数据集 2：

成员 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

对于每个成员，如果他们在数据集 1 中被标记为“新”，但在数据集 1 中的 min_srvc_dt 之前在数据集 2 中没有连续两个成员月份，则翻转为“当前”。

最终结果表应如下所示：

数据集 1：

成员 ID	MIN_SRVC_DT	NEW OR CURRENT
111	202104	NEW
222	201801	CURRENT
333	202105	CURRENT

成员 111 保持为 NEW，因为它们在数据集 2 中具有覆盖月份 202102 和 202103。成员 222 切换到当前，因为最早报道他们从 202101 开始的月份，但他们的 min_srvc_dt 是 201801，因此他们需要 201801 之前 2 个月的覆盖月份（即 201712 和 201711）。成员 333 更改为当前，因为覆盖范围存在差距（他们的覆盖范围为 201909 和 201910，然后是 2021 年，但他们需要 202001 和 201912 的覆盖范围）

我如何在 SQL Server 中对此进行编码？如何使用数据集 1 中的 min_srvc_dt 找到数据集 2 中覆盖范围的差距？

我已经尝试过这个：

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
) 

但这只给出了我正在寻找的成员222。

然后我尝试了这个来尝试找出差距，但它并没有真正给出我正在寻找的东西......

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

根据您的示例数据，您可以使用此查询来更新您的表，我使用 COVERAGE_MONTH 作为 int，您可以将它们用作日期。

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