合并连续的日期范围 [英] Combine consecutive date ranges

问题描述

使用 SQL Server 2008 R2，

Using SQL Server 2008 R2,

鉴于一个结束日期与下一个开始日期相邻，我正在尝试将日期范围合并到最大日期范围中.

I'm trying to combine date ranges into the maximum date range given that one end date is next to the following start date.

这些数据是关于不同的工作的.一些员工可能已经结束雇佣并在稍后重新加入.这些应该算作两个不同的工作(例如 ID 5).有些人有不同类型的工作，相互追逐(结束日期和开始日期并列)，在这种情况下，应将其视为一个工作(例如 ID 30).

The data is about different employments. Some employees may have ended their employment and have rejoined at a later time. Those should count as two different employments (example ID 5). Some people have different types of employment, running after each other (enddate and startdate neck-to-neck), in this case it should be considered as one employment in total (example ID 30).

尚未结束的雇佣期的结束日期为空.

An employment period that has not ended has an enddate that is null.

一些例子可能很有启发性:

Some examples is probably enlightening:

declare @t as table  (employmentid int, startdate datetime, enddate datetime)

insert into @t values
(5, '2007-12-03', '2011-08-26'),
(5, '2013-05-02', null),
(30, '2006-10-02', '2011-01-16'),
(30, '2011-01-17', '2012-08-12'),
(30, '2012-08-13', null),
(66, '2007-09-24', null)

-- expected outcome
EmploymentId StartDate   EndDate
5            2007-12-03  2011-08-26
5            2013-05-02  NULL
30           2006-10-02  NULL
66           2007-09-24  NULL

我一直在尝试不同的孤岛和差距"技术，但一直无法破解这个问题.

I've been trying different "islands-and-gaps" techniques but haven't been able to crack this one.

推荐答案

您在我使用日期 '31211231' 时看到的奇怪之处只是一个非常大的日期来处理您的无结束日期"场景.我假设每个员工不会有很多日期范围，所以我使用了一个简单的递归公用表表达式来组合这些范围.

The strange bit you see with my use of the date '31211231' is just a very large date to handle your "no-end-date" scenario. I have assumed you won't really have many date ranges per employee, so I've used a simple Recursive Common Table Expression to combine the ranges.

为了让它运行得更快，起始锚查询只保留那些不链接到先前范围(每个员工)的日期.其余的只是遍历日期范围并扩大范围.最后的 GROUP BY 仅保留每个起始 ANCHOR(employmentid、startdate)组合建立的最大日期范围.

To make it run faster, the starting anchor query keeps only those dates that will not link up to a prior range (per employee). The rest is just tree-walking the date ranges and growing the range. The final GROUP BY keeps only the largest date range built up per starting ANCHOR (employmentid, startdate) combination.

SQL 小提琴

MS SQL Server 2008 架构设置:

create table Tbl (
  employmentid int,
  startdate datetime,
  enddate datetime);

insert Tbl values
(5, '2007-12-03', '2011-08-26'),
(5, '2013-05-02', null),
(30, '2006-10-02', '2011-01-16'),
(30, '2011-01-17', '2012-08-12'),
(30, '2012-08-13', null),
(66, '2007-09-24', null);

/*
-- expected outcome
EmploymentId StartDate   EndDate
5            2007-12-03  2011-08-26
5            2013-05-02  NULL
30           2006-10-02  NULL
66           2007-09-24  NULL
*/

查询 1:

;with cte as (
   select a.employmentid, a.startdate, a.enddate
     from Tbl a
left join Tbl b on a.employmentid=b.employmentid and a.startdate-1=b.enddate
    where b.employmentid is null
    union all
   select a.employmentid, a.startdate, b.enddate
     from cte a
     join Tbl b on a.employmentid=b.employmentid and b.startdate-1=a.enddate
)
   select employmentid,
          startdate,
          nullif(max(isnull(enddate,'32121231')),'32121231') enddate
     from cte
 group by employmentid, startdate
 order by employmentid

结果:

| EMPLOYMENTID |                        STARTDATE |                       ENDDATE |
-----------------------------------------------------------------------------------
|            5 |  December, 03 2007 00:00:00+0000 | August, 26 2011 00:00:00+0000 |
|            5 |       May, 02 2013 00:00:00+0000 |                        (null) |
|           30 |   October, 02 2006 00:00:00+0000 |                        (null) |
|           66 | September, 24 2007 00:00:00+0000 |                        (null) |

这篇关于合并连续的日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！