T-SQL实现30天复诊客户最新病例筛选需求及代码求助

问题背景 #

我需要处理一组客户诊疗记录，要求保留符合以下条件的条目：

• 若同一客户有多个诊疗记录，且前一条记录的结束日期与后一条的开始日期间隔≤30天（30天内复诊），则仅保留该连续组的最新记录；
• 同时保留结束日期后30天内无后续记录的所有条目。

原始业务数据 #

期望输出结果 #

已尝试的简化代码 #

WITH CTE AS (
 SELECT ClientID
 ,EpisodeID
 ,StartDT
 ,EndDT
 ,Location
 FROM DeNormalized DN)
SELECT CTE.*
FROM CTE
INNER JOIN CTE CTE2 ON CTE.ClientID=CTE2.ClientID
WHERE 1=1 AND DATEDIFF(day,CTE2.EndDT,CTE.StartDT) <= 30

环境说明 #

当前使用托管T-SQL数据库，仅拥有临时对象的DDL权限，需要正确的SQL实现方案。

解决方案代码 #

针对你的需求，我设计了一个基于窗口函数的T-SQL方案，能够准确分组并保留符合条件的记录：

WITH RankedEpisodes AS (
    SELECT 
        ClientID,
        EpisodeID,
        StartDT,
        EndDT,
        Location,
        -- 计算当前记录与前一条记录的间隔天数
        DATEDIFF(day, LAG(EndDT) OVER (PARTITION BY ClientID ORDER BY StartDT), StartDT) AS DaysSinceLastEpisode,
        -- 生成分组ID：当间隔超过30天或为第一条记录时，新建分组
        SUM(CASE 
                WHEN DATEDIFF(day, LAG(EndDT) OVER (PARTITION BY ClientID ORDER BY StartDT), StartDT) > 30 
                     OR LAG(EndDT) OVER (PARTITION BY ClientID ORDER BY StartDT) IS NULL 
                THEN 1 
                ELSE 0 
            END) 
            OVER (PARTITION BY ClientID ORDER BY StartDT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupID
    FROM DeNormalized
),
GroupLatest AS (
    SELECT 
        ClientID,
        GroupID,
        MAX(EpisodeID) AS LatestEpisodeID -- 假设EpisodeID随诊疗时间递增，若不是可改用MAX(StartDT)关联
    FROM RankedEpisodes
    GROUP BY ClientID, GroupID
)
SELECT 
    re.ClientID,
    re.EpisodeID,
    re.StartDT,
    re.EndDT,
    re.Location
FROM RankedEpisodes re
INNER JOIN GroupLatest gl 
    ON re.ClientID = gl.ClientID 
    AND re.EpisodeID = gl.LatestEpisodeID
ORDER BY re.ClientID, re.StartDT;

代码说明 #

1. RankedEpisodes CTE：

   • 按ClientID分组，按StartDT排序，用LAG()函数获取前一条记录的EndDT，计算两条记录的间隔天数；
   • 通过累积求和生成GroupID：当当前记录与前一条的间隔超过30天，或者是该客户的第一条记录时，分组ID加1，这样连续30天内复诊的记录会被分到同一个组。

2. GroupLatest CTE：

   • 按ClientID和GroupID分组，找到每个分组中最新的EpisodeID（如果EpisodeID不随时间递增，可以改用MAX(StartDT)或MAX(EndDT)来匹配对应记录）。

3. 最终查询：

   • 关联两个CTE，筛选出每个分组的最新记录，得到符合需求的结果集。

这个方案完全适配你的托管T-SQL环境，不需要创建永久对象，仅使用临时CTE即可完成计算，符合你的权限限制。

内容的提问来源于stack exchange，提问作者AS91