时间戳

首页 > 解决方案 > 如何在不中断时间的情况下结合开始日期和结束日期？

sql - 如何在不中断时间的情况下结合开始日期和结束日期？

问题描述

我尝试了很多方法，但都没有成功，将记录 ID 相同的开始日期和结束日期结合起来，并将日期中没有中断的地方结合起来 

CREATE TABLE #t (
    A_ID VARCHAR(100),
    BDate VARCHAR(100),
    CDate VARCHAR(100)
)

INSERT INTO #T
  (A_ID, BDate, CDate)
VALUES
('1000','2017/12/01','2017/12/31'),
('1000','2018/01/01','2018/03/31'),
('1000','2018/05/01','2018/05/31')
Select A_ID, bDate,cDate from 
   (
   select BDate,A_ID,Cdate,lead(Bdate) over (order by Bdate) next_BDate from #T as t2
   where   exists ( select null from #T as t1
                       where t1.A_ID = t2.A_ID and t1.Bdate <= t2.Bdate and t1.CDate <=t2.CDate )
   ) as combine

  where bDate < Cdate
    order by BDate;

我想看看：

1000 2017/12/01 2018/03/31 (no break in first two dates) 
1000 2018/05/01 2018/05/31 (Break between 4-1-18 and 5-1-18)

标签： sqlsql-serverssms

解决方案

这是一个差距和孤岛问题，取决于您的实际数据，基于嵌套 OLAP 函数的解决方案可能比递归更有效：

with combine as
 (
   select BDate,A_ID,Cdate,
      -- find the gap and flag it
      case when lag(Cdate)
                over (partition by A_ID
                      order by CDate) = dateadd(day,-1, BDate)
           then 0
           else 1
      end as flag
   from T
 )
, groups as
 (
   Select A_ID, bDate,cDate,
      -- cumulative sum over 0/1 to assign the same group number for row without gaps
      sum(flag)
      over (partition by A_ID
            order by Bdate) as grp
   from combine
 )
-- group consecutive rows into one
select A_ID, min(BDate), max(CDate)
from groups
group by A_ID, grp
order by min(BDate);

推荐阅读