SQL:为每个成员在多个开始和结束日期范围上标识不同的处理块

Question

目的:确定表中每个成员连续治疗的不同发作。每个成员都有诊断和服务日期，并且集被定义为每个连续服务之间的时间小于某个数字的所有服务(在本例中假设为90天)。查询需要循环遍历每一行，计算日期之间的差异，并返回与每集相关的第一个和最后一个日期。目标是根据成员和剧集开始/结束日期对结果进行分组。

以前有人问过一个非常类似的问题，有点帮助。问题是，在定制代码时，返回的表排除了第一个和最后一个记录。我不知道该怎么做。

我的数据现在看起来像这样:

MemberCode       Diagnosis              ServiceDate         
1001   -----        ABC      -----       2010-02-04           
1001   -----        ABC      -----       2010-03-20          
1001   -----        ABC      -----       2010-04-18          
1001   -----        ABC      -----       2010-05-22         
1001   -----        ABC      -----       2010-09-26          
1001   -----        ABC      -----       2010-10-11  
1001   -----        ABC      -----       2010-10-19
2002   -----        XYZ      -----       2010-07-10          
2002   -----        XYZ      -----       2010-07-21
2002   -----        XYZ      -----       2010-11-08
2002   -----        ABC      -----       2010-06-03           
2002   -----        ABC      -----       2010-08-13         

在上述数据中，Member 1001的第一条记录为2010-02-04，直到2010-09-26(新集开始的日期)，连续业务之间的差值不超过90天。因此，成员1001有两个不同的片段:(1)诊断ABC，从2010-02-04到2010-05-22;(2)诊断ABC，从2010-09-26到2010-10-19。

同样，成员2002有三个不同的片段:(1)诊断XYZ，从2010-07-10到2010-07-21，(2)诊断XYZ，开始和结束于2010-11-08，(3)诊断ABC，从2010-06-03到2010-08-13。 所需输出:

MemberCode         Diagnosis       EpisodeStartDate          EpisodeEndDate
1001   -----          ABC   -----     2010-02-04   -----       2010-05-22
1001   -----          ABC   -----     2010-09-26   -----       2010-10-19
2002   -----          XYZ   -----     2010-07-10   -----       2010-07-21
2002   -----          XYZ   -----     2010-11-08   -----       2010-11-08
2002   -----          ABC   -----     2010-06-03   -----       2010-08-13

我已经在这个查询上工作了太长时间，仍然不能得到我所需要的。任何帮助都会很感激。提前感谢!

Answer 1

SQL Server 2012有lag()和累加和函数，这使得编写这样的查询更容易。我们的想法是在每个序列中找到第一个。然后取第一个标志的总和来识别每一组。下面是代码:

select MemberId, Diagnosis, min(ServiceDate) as EpisodeStartDate,
       max(ServiceStartDate) as EpisodeEndDate
from (select t.*, sum(ServiceStartFlag) over (partition by MemberId, Diagnosis order by ServiceDate) as grp
      from (select t.*,
                   (case when datediff(day,
                                       lag(ServiceDate) over (partition by MemberId, Diagnosis
                                                              order by ServiceDate),
                                       ServiceDate) < 90
                         then 0
                         else 1 -- handles both NULL and >= 90
                    end) as ServiceStartFlag
            from table t
           ) t
group by grp, MemberId, Diagnosis;

您可以在早期版本的SQL Server中这样做，但代码更麻烦。

Answer 2

对于2012年之前的SQL Server版本，这里有一些代码片段应该可以工作。首先，您需要一个临时表(与CTE相反，因为查找边缘事件将再次触发newid()函数，而不是检索该行的值)

DECLARE @Edges TABLE (MemberCode INT, Diagnosis VARCHAR(3), ServiceDate DATE, GroupID VARCHAR(40))
INSERT INTO @Edges
SELECT *
FROM Treatments E
    CROSS APPLY (
        SELECT 
            CASE
                WHEN EXISTS (
                    SELECT TOP 1 E2.ServiceDate
                    FROM Treatments E2
                    WHERE E.MemberCode = E2.MemberCode
                        AND E.Diagnosis = E2.Diagnosis
                        AND E.ServiceDate > E2.ServiceDate
                        AND DATEDIFF(dd,E2.ServiceDate,E.ServiceDate) BETWEEN 1 AND 90
                    ORDER BY E2.ServiceDate DESC
                    ) THEN 'Group'
                ELSE CAST(NEWID() AS VARCHAR(40))
            END AS GroupID
    ) z

EXISTS操作符包含一个查询，查询过去1天到90天之间的日期。一旦收集了Edge用例，该查询将提供您根据所发布的测试数据所发布的结果。

SELECT MemberCode, Diagnosis, MIN(ServiceDate) AS StartDate, MAX(ServiceDate) AS EndDate
FROM (
    SELECT
          MemberCode
        , Diagnosis
        , ServiceDate
        , CASE GroupID
            WHEN 'Group' THEN (
                SELECT TOP 1 GroupID
                FROM @Edges E2
                WHERE E.MemberCode = E2.MemberCode
                    AND E.Diagnosis = E2.Diagnosis
                    AND E.ServiceDate > E2.ServiceDate
                    AND GroupID != 'Group'
                ORDER BY ServiceDate DESC
            )
            ELSE GroupID END AS GroupID
    FROM @Edges E
    ) Z
GROUP BY MemberCode, Diagnosis, GroupID
ORDER BY MemberCode, Diagnosis, MIN(ServiceDate)

就像Gordon说的，更麻烦，但如果你的服务器不是SQL 2012或更高版本，它可以做到。