sql - 合并连续的日期范围

Question

使用SQL Server 2008 R2，

我正在尝试将日期范围合并为最大日期范围，因为一个结束日期位于下一个开始日期的旁边。

数据是关于不同的工作的。一些员工可能已经终止工作，并在以后重新加入公司。这些应算作两种不同的工作(示例ID 5)。有些人有不同的工作类型，彼此相继(结束日期和开始日期并驾齐驱)，在这种情况下，应将其视为一种职业(示例ID 30)。

尚未结束的雇用期的结束日期为null。

一些例子可能是有启发性的:

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

我一直在尝试不同的“孤岛和空白”技术，但未能破解这一技术。

Answer 1

您使用日期“31211231”看到的奇怪之处只是处理“无终止日期”情况的一个非常大的日期。我假设您实际上不会为每个员工设置很多日期范围，所以我使用了一个简单的递归公用表表达式来组合这些范围。

为了使其运行更快，启动 anchor 查询仅将那些而不是链接到先前范围(每个员工)的日期保留下来。其余的只是漫步日期范围并扩大范围。最终的GROUP BY仅保留每个起始ANCHOR(employmentid，startdate)组合建立的最大日期范围。

SQL Fiddle

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

Results :

| 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) |