sql-server-2008 - 如何识别 T-SQL 中每个不同成员的多个开始和结束日期范围中的第一个间隔

Question

我一直在做以下工作，但没有得到任何结果，而且截止日期很快就要到了。此外，还有超过一百万行，如下所示。感谢您在以下方面的帮助。

目标:按成员对结果进行分组，并通过组合单独的日期范围来为每个成员构建连续覆盖范围，这些日期范围彼此重叠或连续运行，范围的开始日和结束日之间没有中断。

我有以下格式的数据:

MemberCode  -----   ClaimID   -----       StartDate   -----       EndDate
00001   -----       012345   -----       2010-01-15   -----       2010-01-20
00001   -----       012350   -----       2010-01-19   -----       2010-01-22
00001   -----       012352   -----       2010-01-20   -----       2010-01-25
00001   -----       012355   -----       2010-01-26   -----       2010-01-30
00002   -----       012357   -----       2010-01-20   -----       2010-01-25
00002   -----       012359   -----       2010-01-30   -----       2010-02-05
00002   -----       012360   -----       2010-02-04   -----       2010-02-15
00003   -----       012365   -----       2010-02-15   -----       2010-02-30

...

在上面的成员( 00001 )是一个有效的成员，因为有一个 连续日期范围 来自 2010-01-15 至 2010-01-30 (没有间隙)。请注意 claim ID 012355 此成员的开始日期紧挨着 claim ID 的结束日期 012352 .这仍然有效，因为它形成了一个连续的范围。

但是，成员 ( 00002 ) 应该是无效成员，因为 claim ID 的结束日期之间有 5 天的间隔。 012357 和 claim ID 的开始日 012359

我想要做的是仅获取那些在连续日期范围(每个成员)的每一天都有 claim 的成员的列表，每个成员的 MIN(开始日期)和 Max(结束日期)之间没有间隔特异成员。有差距的成员被丢弃。

提前致谢。

更新:

我已经到了这里。
注: FILLED_DT = Start Date & PresCoverEndDT = End Date

SELECT PresCoverEndDT, FILLED_DT 

FROM 

(

    SELECT DISTINCT FILLED_DT, ROW_NUMBER() OVER (ORDER BY FILLED_DT) RN

    FROM Temp_Claims_PRIOR_STEP_5 T1

    WHERE NOT EXISTS 

            (SELECT * FROM Temp_Claims_PRIOR_STEP_5 T2

            WHERE T1.FILLED_DT > T2.FILLED_DT AND T1.FILLED_DT< T2.PresCoverEndDT 

            AND T1.MBR_KEY = T2.MBR_KEY )

) T1

    JOIN (SELECT DISTINCT PresCoverEndDT, ROW_NUMBER() OVER (ORDER BY PresCoverEndDT) RN

        FROM Temp_Claims_PRIOR_STEP_5 T1

        WHERE NOT EXISTS 

            (SELECT * FROM Temp_Claims_PRIOR_STEP_5 T2

             WHERE T1.PresCoverEndDT > T2.FILLED_DT AND T1.PresCoverEndDT < T2.PresCoverEndDT AND T1.MBR_KEY = T2.MBR_KEY )
) T2

     ON T1.RN - 1 = T2.RN

WHERE   PresCoverEndDT < FILLED_DT 

上面的代码似乎有错误，因为我只得到一行，而且它也不正确。我想要的输出只有 1 列，如下所示:

Valid_Member_Code

00001

00007

00009

... 等等。，

Answer 1

试试这个:http://www.sqlfiddle.com/#!3/c3365/20

with s as
(
  select *, row_number() over(partition by membercode order by startdate) rn
  from tbl
)
,gaps as
(
select a.membercode, a.startdate, a.enddate, b.startdate as nextstartdate
  ,datediff(d, a.enddate, b.startdate) as gap
from s a
join s b on b.membercode = a.membercode and b.rn = a.rn + 1
)
select membercode 
from gaps
group by membercode
having sum(case when gap <= 1 then 1 end) = count(*);

在此处查看查询进度: http://www.sqlfiddle.com/#!3/c3365/20

它是如何工作的，将当前结束日期与其下一个开始日期进行比较并检查日期差距:

with s as
(
  select *, row_number() over(partition by membercode order by startdate) rn
  from tbl
)
select a.membercode, a.startdate, a.enddate, b.startdate as nextstartdate
  ,datediff(d, a.enddate, b.startdate) as gap
from s a
join s b on b.membercode = a.membercode and b.rn = a.rn + 1;

输出:

| MEMBERCODE |  STARTDATE |    ENDDATE | NEXTSTARTDATE | GAP |
--------------------------------------------------------------
|          1 | 2010-01-15 | 2010-01-20 |    2010-01-19 |  -1 |
|          1 | 2010-01-19 | 2010-01-22 |    2010-01-20 |  -2 |
|          1 | 2010-01-20 | 2010-01-25 |    2010-01-26 |   1 |
|          2 | 2010-01-20 | 2010-01-25 |    2010-01-30 |   5 |
|          2 | 2010-01-30 | 2010-02-05 |    2010-02-04 |  -1 |

然后检查成员是否具有相同的 claim 数量，并且与总 claim 没有差距:

with s as
(
  select *, row_number() over(partition by membercode order by startdate) rn
  from tbl
)
,gaps as
(
select a.membercode, a.startdate, a.enddate, b.startdate as nextstartdate
  ,datediff(d, a.enddate, b.startdate) as gap
from s a
join s b on b.membercode = a.membercode and b.rn = a.rn + 1
)
select membercode, count(*) as count, sum(case when gap <= 1 then 1 end) as gapless_count
from gaps
group by membercode;

输出:

| MEMBERCODE | COUNT | GAPLESS_COUNT |
--------------------------------------
|          1 |     3 |             3 |
|          2 |     2 |             1 |

最后，过滤他们，他们的声明中没有空白的成员:

with s as
(
  select *, row_number() over(partition by membercode order by startdate) rn
  from tbl
)
,gaps as
(
select a.membercode, a.startdate, a.enddate, b.startdate as nextstartdate
  ,datediff(d, a.enddate, b.startdate) as gap
from s a
join s b on b.membercode = a.membercode and b.rn = a.rn + 1
)
select membercode 
from gaps
group by membercode
having sum(case when gap <= 1 then 1 end) = count(*);

输出:

| MEMBERCODE |
--------------
|          1 |

请注意，您不需要做 COUNT(*) > 1检测具有 2 个或更多声明的成员。而不是使用 LEFT JOIN ，我们使用 JOIN ，这将自动丢弃尚未进行第二次声明的成员。如果您选择使用 LEFT JOIN，这是版本(更长)相反(与上面相同的输出):

with s as
(
select *, row_number() over(partition by membercode order by startdate) rn
from tbl
)
,gaps as
(
select a.membercode, a.startdate, a.enddate, b.startdate as nextstartdate
,datediff(d, a.enddate, b.startdate) as gap
from s a
left join s b on b.membercode = a.membercode and b.rn = a.rn + 1
)
select membercode 
from gaps
group by membercode
having sum(case when gap <= 1 then 1 end) = count(gap)
and count(*) > 1; -- members who have two ore more claims only

以下是在过滤之前查看上述查询数据的方法:

with s as
(
  select *, row_number() over(partition by membercode order by startdate) rn
  from tbl
)
,gaps as
(
select a.membercode, a.startdate, a.enddate, b.startdate as nextstartdate
  ,datediff(d, a.enddate, b.startdate) as gap
from s a
left join s b on b.membercode = a.membercode and b.rn = a.rn + 1
)
select * from gaps;

输出:

| MEMBERCODE |  STARTDATE |    ENDDATE | NEXTSTARTDATE |    GAP |
-----------------------------------------------------------------
|          1 | 2010-01-15 | 2010-01-20 |    2010-01-19 |     -1 |
|          1 | 2010-01-19 | 2010-01-22 |    2010-01-20 |     -2 |
|          1 | 2010-01-20 | 2010-01-25 |    2010-01-26 |      1 |
|          1 | 2010-01-26 | 2010-01-30 |        (null) | (null) |
|          2 | 2010-01-20 | 2010-01-25 |    2010-01-30 |      5 |
|          2 | 2010-01-30 | 2010-02-05 |    2010-02-04 |     -1 |
|          2 | 2010-02-04 | 2010-02-15 |        (null) | (null) |
|          3 | 2010-02-15 | 2010-03-02 |        (null) | (null) |

编辑 关于要求澄清:

在您的澄清中，您还想包括尚未进行第二次 claim 的成员，请改为这样做: http://sqlfiddle.com/#!3/c3365/22

with s as
(
select *, row_number() over(partition by membercode order by startdate) rn
from tbl
)
,gaps as
(
select a.membercode, a.startdate, a.enddate, b.startdate as nextstartdate
,datediff(d, a.enddate, b.startdate) as gap
from s a
left join s b on b.membercode = a.membercode and b.rn = a.rn + 1
)
select membercode 
from gaps
group by membercode
having sum(case when gap <= 1 then 1 end) = count(gap)
-- members who have yet to have a second claim are valid too
or count(nextstartdate) = 0; 

输出:

| MEMBERCODE |
--------------
|          1 |
|          3 |

技巧是统计成员的 nextstartdate , 如果他们没有下一个开始日期(即 count(nextstartdate) = 0 )，那么他们只是单一 claim 并且也有效，那么只需附上这个 OR健康)状况:

or count(nextstartdate) = 0; 

实际上，下面的条件也足够了，不过我想让查询更加自我记录，因此我建议依靠成员的 nextstartdate。这是计算尚未进行第二次 claim 的成员的替代条件:

or count(*) = 1;

顺便说一句，我们也必须改变这个比较:

sum(case when gap <= 1 then 1 end) = count(*)

对此(因为我们现在正在使用 LEFT JOIN):

sum(case when gap <= 1 then 1 end) = count(gap)