sql-server - 带有中间记录的 SQL Server 最小值最大值

Question

如果这个问题之前已经回答过，我很抱歉，但我找不到它。也许是我的坏关键字。

我有这张桌子：

CREATE TABLE test1 
(
    Employee VARCHAR(10),
    Band VARCHAR(10),
    StartDate DATE,
    EndDate DATE
)

INSERT INTO test1 
VALUES ('Emp1', 'Band1', '2009-01-01', '2010-12-31'),
       ('Emp1', 'Band1', '2011-01-01', '2012-12-31'), 
       ('Emp1', 'Band1', '2013-01-01', '2013-08-31'),
       ('Emp1', 'Band2', '2013-09-01', '2013-12-31'),
       ('Emp1', 'Band2', '2014-01-01', '2014-06-30'),
       ('Emp1', 'Band1', '2014-07-01', '2014-12-31'),
       ('Emp1', 'Band1', '2015-01-01', '2018-08-31'),
       ('Emp2', 'Band1', '2012-01-01', '2014-12-31'),
       ('Emp2', 'Band1', '2015-01-01', '2018-03-31')

此表中的结果：

Employee   Band   StartDate   EndDate
----------------------------------------
Emp1       Band1  2009-01-01  2010-12-31
Emp1       Band1  2011-01-01  2012-12-31
Emp1       Band1  2013-01-01  2013-08-31
Emp1       Band2  2013-09-01  2013-12-31
Emp1       Band2  2014-01-01  2014-06-30
Emp1       Band1  2014-07-01  2014-12-31
Emp1       Band1  2015-01-01  2018-08-31
Emp2       Band1  2012-01-01  2014-12-31
Emp2       Band1  2015-01-01  2018-03-31

我要创建的是一个结果表，每个结果表employee用band和 minimumstart date和 maximum进行分组end date，但是当中间记录 ( band)存在于相似之间的中间时band，end date应该设置上限并且start date下一个相似组的band应该再次重置。

Employee   Band   StartDate   EndDate
----------------------------------------
Emp1       Band1  2009-01-01  2013-08-31
Emp1       Band2  2013-09-01  2014-06-30
Emp1       Band1  2014-07-01  2018-08-31
Emp2       Band1  2012-01-01  2018-03-31

我已经尝试 CTE 来获取每个波段的最大值和最小值，并将其与原始表进行比较，但我仍然失败了。我也尝试使用领先和滞后，但仍然失败。

很高兴有

假设第二条记录EndDate是2012-02-01，我更喜欢结果仍然是第一个 Band1 组的一条记录。

Employee   Band   StartDate   EndDate
----------------------------------------
Emp1       Band1  2009-01-01  2013-08-31

结果的每一行仅在 中有所不同Band，我将能够计算出员工在移动到另一个乐队（不同的乐队）之前在某个乐队停留了多长时间。

但这是一件好事。

标签： sql-serveraggregateaggregate-functions

Answer 1

这通常称为间隙和岛屿。

一种方法

例子

Declare @YourTable Table ([Employee] varchar(50),[Band] varchar(50),[Start] date,[End] date)
Insert Into @YourTable Values 
 ('Emp1','Band1','2009-01-01','2010-12-31')
,('Emp1','Band1','2011-01-01','2012-12-31')
,('Emp1','Band1','2013-01-01','2013-08-31')
,('Emp1','Band2','2013-09-01','2013-12-31')
,('Emp1','Band2','2014-01-01','2014-06-30')
,('Emp1','Band1','2014-07-01','2014-12-31')
,('Emp1','Band1','2015-01-01','2018-08-31')
,('Emp2','Band3','2012-01-01','2014-12-31')
,('Emp2','Band3','2015-01-01','2018-03-31')

;with cte as (
Select *,Grp = sum(Flg) over (Partition By Employee Order by [End])
 From (
        Select *,Flg = IsNull(datediff(DAY,Lag([End],1) over (Partition By Employee,Band Order by [End]) ,[Start]) - 1,1)
         From  @YourTable
      ) A
)
Select Employee
      ,Band
      ,[Start] = min([Start])
      ,[End]   = max([End])
 From cte
 Group By Employee,Band,Grp
 Order by Employee,max([End])

退货

Employee    Band    Start       End
Emp1        Band1   2009-01-01  2013-08-31
Emp1        Band2   2013-09-01  2014-06-30
Emp1        Band1   2014-07-01  2018-08-31
Emp2        Band3   2012-01-01  2018-03-31

如果它有助于可视化，CTE 会生成以下内容

注意标志和组列