时间戳

首页 > 解决方案 > SQL：返回日期跨度有间隙

sql - SQL：返回日期跨度有间隙

问题描述

我在一家健康保险公司工作，并且正在尝试创建一个查询来输出成员的资格范围。源表列出了成员有资格获得的所有月份的历史日期，每月一次。例如，如果成员 55555 从 2018 年 1 月 15 日至 2018 年 5 月 15 日符合资格，则将有五个条目。

如果不是因为资格上的差距，这将很容易解决（有最小值和最大值），这使得它变得更加棘手。理想情况下，我希望每个成员都有一个单独的行，其中包含他们的资格开始和结束日期。因此，如果前一个成员的资格跨度在 7 月恢复并一直持续到 8 月，则应该返回两行：

Member_Nbr    Start_Date    End_Date
55555         01/15/2018    05/15/2018
55555         07/15/2018    08/15/2018


----------Sample Dataset:

create table #gaps (member_nbr varchar(5),membership_date date)
insert into #gaps(member_nbr,membership_date)
values ('55555','01/15/2018'),('55555','02/15/2018'),('55555','03/15/2018'), 
('55555','04/15/2018'),('55555','05/15/2018'),('55555','07/15/2018'), 
('55555','08/15/2018')

----------Without Gaps
select member_nbr,min(membership_date),max(membership_date)
from #gaps
group by member_nbr
order by member_nbr

----------With Gaps (desired output)

有没有办法以所需的方式恢复资格日期？

谢谢

比利

标签： sqlsql-servergaps-and-islands

解决方案

这不是一个特别难的问题。这是一个非常简单的解决方案：

select member_nbr, min(membership_date), max(membership_date)
from (select g.*, row_number() over (partition by member_nbr order by membership_date) as seqnum
      from gaps g
     ) g
group by member_nbr, dateadd(month, - seqnum, membership_date);

连同一个db<>fiddle。

这个想法很简单。如果您有一个日期并减去一系列月数，则结果对于相邻月份的序列是恒定的。所以：

2018-02-15   1    2018-01-15
2018-03-15   2    2018-01-15
2018-04-15   3    2018-01-15
2018-07-15   4    2018-03-15
2018-08-15   5    2018-03-15
2018-11-15   6    2018-05-15

最后一列用于聚合。

日期并非全在 15 日会导致问题。使用诸如eomonth().

推荐阅读