SQL Server中第一条记录的结束日期和下一条记录的开始日期重叠

Question

我有以下类型的数据，我需要以下类型的输出。

输入：

id   startdate  enddate
1    21/01/2019 23/01/2019
1    23/01/2019 24/01/2019
1    24/01/2029 27/01/2019
1    29/01/2019 02/02/2019

输出：

id  startdate   enddate
1   21/01/2019  27/01/2019
1   29/01/2019  02/02/2019

我们需要使用匹配第一个记录enddate和第n个记录startdate的逻辑。

Answer 1

这是一个缺口和孤岛问题，您需要将其组合在一起"相邻的"；日期。这里有一种使用窗口函数的方法：其思想是将当前开始日期与"窗口"的结束日期进行比较；先前的"；行，并使用窗口和来定义组：

select id, min(startdate) startdate, max(enddate) enddate
from (
select t.*,
sum(case when startdate = lag_enddate then 0 else 1 end) over(partition by id order by startdate) grp
from (
select t.*,
lag(enddate) over(partition by id order by startdate) lag_enddate
from mytable t
) t
) t
group by id, grp

DB Fiddle上的演示-Sander首先创建了DDL语句：

id|startdate|enddate-：|：--------|：---------2019-01-21|2019-01-271|2019-01-29|2019-02-02

Answer 2

查看

1. NEXT VALUE FOR方法，适用于2016年及以后的

2. 使用CTE或子查询(适用于2008年(，使用上一个值作为联接来联接自己的表。这里是我使用的一个示例脚本，显示备份增长

   declare @backupType char(1)
   , @DatabaseName sysname
   set @DatabaseName   = db_name() --> Name of current database, null for all databaseson server
   set @backupType     ='D'        /* valid options are:
   D = Database 
   I = Database Differential 
   L = Log 
   F = File or Filegroup 
   G = File Differential 
   P = Partial 
   Q = Partial Differential
   */
   
   select  backup_start_date
   , backup_finish_date
   , DurationSec
   , database_name,backup_size
   , PreviouseBackupSize
   , backup_size-PreviouseBackupSize as growth
   ,KbSec= format(KbSec,'N2')
   FROM (
   select backup_start_date 
   , backup_finish_date
   , datediff(second,backup_start_date,b.backup_finish_date) as DurationSec 
   , b.database_name
   , b.backup_size/1024./1024. as backup_size
   ,case when datediff(second,backup_start_date,b.backup_finish_date) >0 
   then ( b.backup_size/1024.)/datediff(second,backup_start_date,b.backup_finish_date)
   else 0 end as KbSec
   --   , b.compressed_backup_size
   , (      
   select top (1) p.backup_size/1024./1024. 
   from msdb.dbo.backupset p 
   where p.database_name = b.database_name
   and p.database_backup_lsn< b.database_backup_lsn
   and type=@backupType
   order by p.database_backup_lsn desc
   ) as PreviouseBackupSize
   from msdb.dbo.backupset as b
   where @DatabaseName IS NULL OR database_name =@DatabaseName
   and type=@backupType
   )as A
   order by backup_start_date desc
   

3. 使用"；游标本地fast_forward"；逐行循环数据，并使用临时表存储&compare前值

Answer 3

这里有一个可以使用公共表表达式的解决方案。

样本数据

create table data
(
id int,
startdate date,
enddate date
);
insert into data (id, startdate, enddate) values
(1, '2019-01-21', '2019-01-23'),
(1, '2019-01-23', '2019-01-24'),
(1, '2019-01-24', '2019-01-27'),
(1, '2019-01-29', '2019-02-02');

解决方案

-- determine start dates
with cte_start as
(
select s.id,
s.startdate
from data s
where not exists ( select 'x'
from data e
where e.id = s.id
and e.enddate = s.startdate )
),
-- determine date boundaries
cte_startnext as
(
select s.id,
s.startdate,
lead(s.startdate) over (partition by s.id order by s.startdate) as startdate_next
from cte_start s
)
-- determine periods
select sn.id,
sn.startdate,
e.enddate
from cte_startnext sn
cross apply ( select top 1 e.enddate
from data e
where e.id = sn.id
and  e.startdate >= sn.startdate
and (e.startdate <  sn.startdate_next or sn.startdate_next is null)
order by e.enddate desc ) e
order by sn.id,
sn.startdate;

结果

id startdate  enddate
-- ---------- ----------
1  2019-01-21 2019-01-27
1  2019-01-29 2019-02-02

查看解决方案的构建和中间CTE结果。