Merging absence periods

Question

I have the following dataset:

;with Data AS
(
select '001' AS PERSONNUM,'2017-09-18 00:00:00.000' AS START,'2017-09-21 00:00:00.000' AS [END]
UNION
select '001','2017-09-22 00:00:00.000' AS START,'2017-09-22 00:00:00.000' AS [END]
UNION
select '002','2017-09-18 00:00:00.000' AS START,'2017-09-20 00:00:00.000' AS [END]
UNION
select '002','2017-09-22 00:00:00.000' AS START,'2017-09-22 00:00:00.000' AS [END]
)
select * from Data

I need to merge records if the difference in days between START column from next row and END column from previous column for some employee (PERSONNUM) is equal to 1. In my examples, for person '001' records should be merged, for person '002' - no changes.

So, expected results will be the following:

Thank you in advance!

Possible duplicate of [Combine consecutive date ranges](https://stackoverflow.com/questions/15783315/combine-consecutive-date-ranges) — Tab Alleman, Sep 29 '17 at 13:38
@TabAlleman - Same problem but there are better, more efficient, solutions than the one that's been marked as the answer. This is a better place to get started... http://sqlmag.com/sql-server/new-solution-packing-intervals-problem — Jason A. Long, Sep 29 '17 at 16:03
@JasonA.Long Then I suggest, if you wish, to create an answer elaborating about that in the linked question ! This question seems really a duplicate, so will probably get closed as one. Your insights are interesting for everyone, but their place is in the other question. — Pac0, Sep 29 '17 at 16:47

score 2 · Answer 1 · answered Sep 29 '17 at 18:05

Something along these line... Basically taken directly from Itzik Ben-Gan's New Solution to the Packing Intervals Problem

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    PersonId CHAR(3),
    BegDT DATETIME,
    EndDT DATETIME
    );
INSERT #TestData(PersonId, BegDT, EndDT) VALUES
    (1, '20151231 08:00:00', '20151231 08:30:00'),
    (1, '20151231 08:30:00', '20151231 09:00:00'),
    (1, '20151231 09:00:00', '20151231 09:30:00'),
    (1, '20151231 10:00:00', '20151231 11:00:00'),
    (1, '20151231 10:30:00', '20151231 12:00:00'),
    (1, '20151231 11:30:00', '20151231 12:30:00'),
    (2, '20151231 08:00:00', '20151231 10:30:00'),
    (2, '20151231 08:30:00', '20151231 10:00:00'),
    (2, '20151231 09:00:00', '20151231 09:30:00'),
    (2, '20151231 11:00:00', '20151231 11:30:00'),
    (2, '20151231 11:32:00', '20151231 12:00:00'),
    (2, '20151231 12:04:00', '20151231 12:30:00'),
    (3, '20151231 08:00:00', '20151231 09:00:00'),
    (3, '20151231 08:00:00', '20151231 08:30:00'),
    (3, '20151231 08:30:00', '20151231 09:00:00'),
    (3, '20151231 09:30:00', '20151231 09:30:00');

WITH
    cte_MPE AS (
        SELECT 
            td.PersonId, td.BegDT, td.EndDT,
            MaxPrevEnd = MAX(td.EndDT) OVER (PARTITION BY td.PersonId ORDER BY td.BegDT, td.EndDT ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
        FROM
            #TestData td
        ),
    cte_DivGroup AS (
        SELECT 
            m.PersonId, m.BegDT, m.EndDT, m.MaxPrevEnd,
            DivGroup = SUM(x.NewBeg) OVER (PARTITION BY m.PersonId ORDER BY m.BegDT, m.EndDT ROWS UNBOUNDED PRECEDING)
        FROM
            cte_MPE m
            CROSS APPLY ( VALUES (IIF(m.BegDT <= m.MaxPrevEnd, NULL, 1)) ) x (NewBeg)
        )
SELECT 
    dg.PersonId, 
    BegDT = MIN(dg.BegDT), 
    EndDT = MAX(dg.EndDT)
FROM
    cte_DivGroup dg
GROUP BY
    dg.PersonId,
    dg.DivGroup
ORDER BY
    dg.PersonId;

Results...

PersonId BegDT                   EndDT
-------- ----------------------- -----------------------
1        2015-12-31 08:00:00.000 2015-12-31 09:30:00.000
1        2015-12-31 10:00:00.000 2015-12-31 12:30:00.000
2        2015-12-31 08:00:00.000 2015-12-31 10:30:00.000
2        2015-12-31 11:00:00.000 2015-12-31 11:30:00.000
2        2015-12-31 11:32:00.000 2015-12-31 12:00:00.000
2        2015-12-31 12:04:00.000 2015-12-31 12:30:00.000
3        2015-12-31 08:00:00.000 2015-12-31 09:00:00.000
3        2015-12-31 09:30:00.000 2015-12-31 09:30:00.000

Thank you a lot, Jason. I've slightly modified approach above and got correct results. — David Freeman, Oct 02 '17 at 08:12

Merging absence periods

1 Answers1