merging date periods when the difference of adjacent periods are less than x(eg x=3) days

Question

How to merge date periods when the difference between the adjacent periods are less than x(eg x=3) days.In the example below, difference between the start date of period 3 and end date of period 2 is 2.So, third and second periods are to be merged as single period as shown below.But the difference between the start date of period 4 and end date of period 3 is 3. So, they are not merged and treated as separate periods.

startdate                  enddate
-------------------------------------------------------
2004-12-29 00:00:00.000 2004-12-29 00:00:00.000
2005-11-25 00:00:00.000 2005-11-25 00:00:00.000
2005-11-27 00:00:00.000 2005-12-09 00:00:00.000
2005-12-12 00:00:00.000 2005-12-17 00:00:00.000
2005-12-19 00:00:00.000 2005-12-20 00:00:00.000

Output:

 startdate                  enddate
---------------------------------------------------------

2004-12-29 00:00:00.000 2004-12-29 00:00:00.000
2005-11-25 00:00:00.000 2005-12-09 00:00:00.000
2005-12-12 00:00:00.000 2005-12-20 00:00:00.000

Answer 1

You first need to identify the records that are adjacent. For these records you only need to keep one record with the combined date range, the other records can simply be passed on:

 ;WITH  adjacent
        AS ( SELECT a.startdate AS startDateA ,
                    a.enddate AS endDateA ,
                    b.startdate AS startDateB ,
                    b.enddate AS endDateB
            FROM   dbo.data a
                   LEFT JOIN dbo.data b ON DATEDIFF(day, a.enddate,
                                             b.startdate) <= 3
                                      AND a.startdate < b.startdate
          )
   SELECT a.startDateA AS StartDate ,
          ISNULL(a.endDateB, a.endDateA) AS EndDate
   FROM  adjacent a
   LEFT JOIN adjacent b ON  a.startDateA = b.startDateB
                             AND a.endDateA = b.endDateB                                
   WHERE b.startDateA IS NULL 
   ORDER BY a.startDateA

This is the test script I have used:

CREATE TABLE dbo.data(startdate DATETIME, enddate DATETIME)


INSERT INTO dbo.data
      SELECT '2 Jan 2015' ,
             '3 Jan 2015'
      UNION
      SELECT '6 Jan 2015' ,
             '6 Jan 2015'
      UNION
      SELECT  '10 Jan 2015' ,
              '11 Jan 2015'

EDIT: If you require multiple date ranges to be merged in a function (for example x=10), the above solution will not work and instead you have to use a recursive cte like below. As per your comments, I also refactored this code inside a function:

CREATE TYPE daterangetype AS TABLE
(
startdate DATETIME NOT NULL,
enddate DATETIME NOT NULL
)
GO
CREATE FUNCTION dbo.fn_Merge_DateRanges
    (
     @data daterangetype READONLY ,
     @dayrange INT 
    )
RETURNS @merged TABLE
    (
     startdate DATETIME ,
     endate DATETIME
    )
AS 
    BEGIN
       WITH  cte0
               AS ( SELECT    ROW_NUMBER() OVER ( ORDER BY startdate ) AS RowId ,
                          startdate ,
                          enddate
                   FROM   @data
                 ),
             cte1
               AS ( SELECT    t.RowId ,
                          t.startdate ,
                          t.enddate ,
                          t2.startdate AS Nextstartdate
                   FROM   cte0 AS t
                          LEFT OUTER JOIN cte0 AS t2 ON t2.RowId > t.RowId
                                                  AND DATEDIFF(day,
                                                  t.enddate,
                                                  t2.startdate) < @dayrange
                 ),
             cte2
               AS ( SELECT    c.RowId ,
                          c.startdate ,
                          c.enddate ,
                          c.Nextstartdate
                   FROM   cte1 AS c
                   WHERE      c.Nextstartdate IS NULL
                   UNION ALL
                   SELECT     c2.RowId ,
                          c.startdate ,
                          c2.enddate ,
                          c2.Nextstartdate
                   FROM   cte2 AS c2
                          INNER JOIN cte1 AS c ON c.Nextstartdate = c2.startdate
                 )
          INSERT    INTO @merged
                SELECT  MIN(startdate) AS startdate ,
                       enddate
                FROM       cte2
                GROUP BY RowId ,
                       enddate
                ORDER BY RowId ,
                       startdate

       RETURN

    END

You can then call your function like this:

DECLARE @data daterangetype

SET DATEFORMAT YMD

INSERT  INTO @data
       SELECT    '2004-12-29 00:00:00.000' ,
             '2004-12-29 00:00:00.000'
       UNION ALL
       SELECT    '2005-11-25 00:00:00.000' ,
             '2005-11-25 00:00:00.000'
       UNION ALL
       SELECT    '2005-11-27 00:00:00.000' ,
             '2005-12-09 00:00:00.000'
       UNION ALL
       SELECT    '2005-12-12 00:00:00.000' ,
             '2005-12-17 00:00:00.000'
       UNION ALL
       SELECT    '2005-12-19 00:00:00.000' ,
             '2005-12-20 00:00:00.000'    


SELECT  * FROM     dbo.fn_Merge_DateRanges(@data, 5)

Answer 2

set dateformat dmy;
declare @t table (id int identity, startdate smalldatetime, enddate smalldatetime);
insert into @t (startdate, enddate) values ('02.01.2015', '03.01.2015');
insert into @t (startdate, enddate) values ('06.01.2015', '06.01.2015');
insert into @t (startdate, enddate) values ('10.01.2015', '11.01.2015');
declare @x int = 3;
declare @toDel table(id int);

update
  a
set
  a.enddate = b.enddate
output
  b.id
-- save merged id, than should be deleted
into
  @toDel (id)
from
  @t a
  join @t b on datediff(day, a.enddate, b.startdate) between 0 and @x
               and a.id != b.id
;


-- delete merged id
delete from @t where id in (select d.id from @toDel d);

select * from @t;