sql-server - 获取多行的天数

Question

这是针对 SQL Server 2008/2012 的。

我有以下数据集，其中包含 claim 开始日期和结束日期。我想计算连续 claim 的天数，其中下一个日期的 claim 开始日期是前一个日期的 claim 结束日期之后的一天，使其成为连续服务。

如果服务出现中断，例如成员(member) ID 1002 的 claim 结束日期为 05/15，而下一个 claim 从 05/18 开始，则应重新开始计数。

MemberID    Claim Start   Claim End      Claim_ID
1001        2016-04-01    2016-04-15     ABC11111
1001        2016-04-16    2016-04-30     ABC65465
1001        2016-05-01    2016-05-15     ABC51651
1001        2016-05-16    2016-06-15     ABC76320
1002        2016-04-01    2016-04-15     ABC74563
1002        2016-04-16    2016-04-30     ABC02123
1002        2016-05-01    2016-05-15     ABC02223
1002        2016-05-18    2016-06-15     ABC66632
1002        2016-06-16    2016-06-30     ABC77447
1002        2016-07-10    2016-07-31     ABC33221
1002        2016-08-01    2016-08-10     ABC88877

如此有效，我想要以下输出。第一次 claim 开始日期的最小值，当多个 claim 之间的承保范围没有差距时， claim 结束日期的最大值。如果承保范围存在差距，则从第一次 claim 开始日期的最小值和 claim 结束日期的最大值开始计算，直到多次 claim 之间的承保范围没有差距。

MemberID    Claim_Start   Claim_End     Continuous_Service_Days
1001        2016-04-01    2016-06-15    76
1002        2016-04-01    2016-05-15    45
1002        2016-05-18    2016-06-30    44 
1002        2016-07-10    2016-08-10    32

我尝试了 while 循环、CTE，并且我还尝试了下表以首先获取声明之间的所有日期。但是我在计算连续日期之间的天数以及在覆盖范围中断时重置计数时遇到问题。

Master.dbo.spt_values

感谢任何帮助。谢谢!

Answer 1

你需要先找到差距。

此解决方案使用 Tally Table首先生成从 ClaimStart 到 ClaimEnd 的日期。然后使用生成的日期，使用 this method 获取间隔.

现在您已经有了差距，您现在可以使用 GROUP BY 来获取 MIN(ClaimStart) 和 MAX(ClaimStart):

WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
CteTally(N) AS(
    SELECT TOP(SELECT MAX(DATEDIFF(DAY, ClaimStart, ClaimEnd) + 1) FROM tbl) 
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
),
CteDates AS( -- Generate the dates from ClaimStart to ClaimEnd
    SELECT
        t.MemberID,
        dt = DATEADD(DAY, ct.N - 1, t.ClaimStart)
    FROM tbl t
    INNER JOIN CteTally ct
        ON DATEADD(DAY, ct.N - 1, t.ClaimStart) <= t.ClaimEnd
),
CteGrp AS( -- Find gaps and continuous dates
    SELECT *,
        rn = DATEADD(DAY, - ROW_NUMBER() OVER(PARTITION BY MemberID ORDER BY dt), dt)
    FROM CteDates
)
SELECT
    MemberID,
    ClaimStart  = MIN(dt),
    ClaimEnd    = MAX(dt),
    Diff        = DATEDIFF(DAY, MIN(dt), MAX(dt)) + 1
FROM CteGrp
GROUP BY MemberID, rn
ORDER BY MemberID, ClaimStart;

ONLINE DEMO