How to combine start and end dates without breaks in time?

Question

I have tried many ways, but unsuccessfully, to combine Start dates and end dates where the record Id is the same and combine the where there is no break in the Date

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;

I would like to see:

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)

Possible duplicate of [SQL - combine consecutive date rows based on column](https://stackoverflow.com/questions/32318255/sql-combine-consecutive-date-rows-based-on-column) — hatchet - done with SOverflow, May 04 '18 at 19:02
@hatchet read that duplicate more carefully, it's not really the same, although I am sure there is an appropriate duplicate somewhere on the site. I've seen this question asked at least a dozen times. — Tab Alleman, May 04 '18 at 19:05
Shouldn't this read '1000 2018/05/01 2018/05/31 (Break between 4-1-18 and 5-1-18)'? — Daniel Marcus, May 04 '18 at 19:06
Possible duplicate of [Combine consecutive date ranges](https://stackoverflow.com/questions/15783315/combine-consecutive-date-ranges) — Tab Alleman, May 04 '18 at 19:07
@hatchet I tried that but it doesn't account for the break so it gives me 2017-12-01 - 2018-05-31 — Dave17003, May 04 '18 at 19:26

score 1 · Accepted Answer · answered May 04 '18 at 19:36

This is a gaps & islands problem, depending on your actual data a solution based on b´nested OLAP-functions might be more efficient that recursion:

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

score 0 · Answer 2 · answered May 04 '18 at 19:46

0

How does this work for you?

declare @table table (a_id int, bdate date, cdate date, id int)
    insert @table
    select a_id, bdate, cdate, 
    case when lag(cdate, 1,cdate) over(partition by a_id order by bdate)  in (cdate, dateadd(day, -1, bdate))
    then 1 else 2 end id from #t 


    select a.a_id, min(a.bdate)bdate, max(a.cdate)cdate from @table a
    left join 
    @table b
    on a.id=b.id and a.a_id=b.a_id and b.id=1
    group by a.a_id, a.id

answered May 04 '18 at 19:46

Daniel Marcus

2,686
1
7
13

now that I have the dates corrected is it possible to combine 2 tables and report where the second table has an extra date Example: Table 1 a_id bdate cdate 1000 2017-12-01 2018-03-31 1000 2018-05-01 2018-05-31 Table 2 1000 2017/12/01 2018/03/31 1000 2018/05/01 2018/05/31 1000 2018/04/01 2018/04/28 – Dave17003 May 07 '18 at 20:04
Not following you exactly - can you draw it out a little more? – Daniel Marcus May 07 '18 at 20:06
Table #2 has the same ID as Table #1 however Table #2 has one additional record with a different bdate. I need to identify that one record. – Dave17003 May 08 '18 at 13:24

How to combine start and end dates without breaks in time?

2 Answers2