如何选择符合Server中某些条件的特定前一行

Question

我有一个带有PatientID、DiagnosisID和Date列的表。这种情况是，如果病人在一个月内多次来做相同的诊断，那么只有日期差大于或等于3的行才应计算为该月份的访问计数。

例子：

RowNumber PatientID DiagnosisID DiagnosisDate
1            P1         D1        29-12-2018
2            P1         D1        01-01-2019
3            P1         D1        05-01-2019
4            P1         D1        06-01-2019
5            P1         D1        08-01-2019
6            P1         D1        09-01-2019
7            P1         D1        13-01-2019
8            P1         D1        31-01-2019
9            P1         D1        01-02-2019
10           P1         D1        07-02-2019

1. 参观人数应计算为2018年12月1日。
2. DateDiff>=3在第1排和第2排(12月29日至1月1日)之间，因此1月1日是有效的访问。
3. DateDiff>=3在第2排到第3排(1月1日至5日)之间，因此1月5日是有效的访问。
4. 从第3排到第4排(1月5日至6日)之间的DateDiff<=3，因此1月6日不是有效的访问。
5. DateDiff<=3在第4行和第5行之间(1月6日和8日)，因此应该将8月8日与以前的有效访问(即这里的5月5日)进行比较，datediff>=3因此8月1月是新的有效访问，前面的行应该与8月8日进行比较。
6. DateDiff<=3在第5排到第6排(1月8日至9日)之间，因此1月9日不是有效的访问。
7. DateDiff>=3在第6排到第7排(1月13日至9日)之间，因此，1月13日是新的有效访问。
8. DateDiff>=3在第7排到第8排(1月31日至13日)之间，因此，1月31日是新的有效访问。
9. 从第8排到第9排(2月1日至1月31日)之间的DateDiff<=3，因此2月1日不是有效的访问。
10. DateDiff>=3在第9排和第10排之间(2月7日和2月1日)，因此2月7日是一次有效的访问。

最后产出：

Dec-2018 visit count = 1 (29th Dec)
Jan-2019 visit count = 5 (1, 5, 8, 13 & 31st Jan)
Feb-2019 visit count = 1 ( 7th Feb )

基本上，当前行和prev行之间的Datediff应该大于3，如果此条件为false，则应将当前行与之前的有效访问进行比较。

我尝试过用递归的CTE实现它，但是我无法找到一个终止条件，即比较应该在之前的有效访问中停止。

Answer 1

用滞后函数测试DATEDIFF

WITH test_tab as
(
select RowNumber, PatientID, DiagnosisID, DiagnosisDate,
DATETIFF(day, lag(PlantFK,1,'19900101') OVER (PARTITION BY PatientID, DiagnosisID order by DiagnosisDate),DiagnosisDate) days
from table
)
Select * from test_tab ;

filtler >=3

WITH test_tab as
    (
    select RowNumber, PatientID, DiagnosisID, DiagnosisDate,
    DATETIFF(day, lag(PlantFK,1,'19900101') OVER (PARTITION BY PatientID, DiagnosisID order by DiagnosisDate),DiagnosisDate) days
    from table
    )
    Select * from test_tab WHERE days>=3

和按年/月分组

WITH test_tab as
        (
        select RowNumber, PatientID, DiagnosisID, DiagnosisDate,
        DATETIFF(day, lag(PlantFK,1,'19900101') OVER (PARTITION BY PatientID, DiagnosisID order by DiagnosisDate),DiagnosisDate) days
        from table
        )
Select YEAR(DiagnosisDate) year,Month(DiagnosisDate) montch,Count(RowNumber)
     from test_tab 
    WHERE days>=3
    GROUP BY YEAR(DiagnosisDate) ,Month(DiagnosisDate)