Grouping multiple periods into one period

Question

I have a list of time frames. Each record has an ID, Start and End date/time.

Several records are back to back. Meaning the Ending Time is the starting time of the next record. I need to join these records together to form one long period.

I have tried Lag/Lead functions, to try and group by the initial ID with out any success.

Here is a screenshot of the data. The "IsInAGroup" is my derived column using lag/lead....

SELECT ID ,PeriodStart ,PeriodEnd ,CASE WHEN LEAD(PeriodStart) over (partition by REG_NUMBER order by PeriodStart) =PeriodEnd THEN 1 --ID WHEN LAG(PeriodEnd) over (partition by REG_NUMBER order by PeriodStart) =PeriodStart THEN --ID 1 ELSE NULL END as "IsInAGroup" FROM #tmpACTIVITIES WHERE REG_NUMBER='ABC123' AND PeriodStart >='6/1/2018' ORDER BY 2

Should `IsInAGroup` have zeros and ones (representing false and true) or separate "group numbers"? — The Impaler, Oct 24 '18 at 15:31
FROM your NULL and 1 , you can query the set as an 'islands' problem, where you generate the start and end time of each island. You could then UNION ALL that with the NULLs which are not islands — Cato, Oct 24 '18 at 15:35
Please post your attempt to use LAG/LEAD so that we can debug it. If you do it correctly, it can lead to a solution. — Tab Alleman, Oct 24 '18 at 15:43
[code] ,CASE WHEN LEAD(PeriodStart) over (partition by REG_NUMBER order by PeriodStart) =PeriodEnd THEN 1 --ID WHEN LAG(PeriodEnd) over (partition by REG_NUMBER order by PeriodStart 1 --ID ELSE NULL END as "IsInAGroup" [/code] — frmrock164, Oct 24 '18 at 17:03
What I was thinking was trying to get the ID of the first period and apply that to all 4 . Then I could easily group by that new column and get the min of the start period and the max of the end period. — frmrock164, Oct 24 '18 at 17:04

JohnLBevan · Answer 1 · 2018-10-24T20:25:48.010

Would this do the trick? i.e. you just want to set IsInAGroup to 1 where there exists a different record which has a start or end period matching the record in question's end or start period:

update a
set IsInAGroup = 1
from myTable a
where exists 
(
    select top 1 1
    from myTable b
    where b.Id != a.Id --it's a different record
    and 
    (
        b.PeriodEnd = a.PeriodStart --but the record is immediately before our record
        or b.PeriodStart = a.PeriodEnd --or the record is immediately after
    )
)

Update

Per comments, if you're looking to "squash" a bunch of records into a single one, try going with a recursive CTE.

with cte as
(
    --get all periods which don't immediately follow another period
    --these are the first record in the group (including groups of 1 record)
    --NB: assumes that a single record cannot have its PeriodStart = its own PeriodEnd
    select Id, PeriodStart, PeriodEnd, 1 Iteration 
    from myTable
    where PeriodStart not in (select PeriodEnd from myTable)

    union all

    --recursively get each period with a start date matching the last record's end date.
    --persist the original id and start date, use the new record's end date, add 1 to the iteration column each recursion
    select cte.Id, cte.PeriodStart, mt.PeriodEnd, cte.Iteration + 1 
    from cte
    inner join myTable mt on mt.PeriodStart = cte.PeriodEnd
)
, cte2 as 
(
    --get all records / invert the Iteration (so the last record in a window has value r=1)
    select id, PeriodStart, PeriodEnd, row_number() over (partition by id order by Iteration desc) r
    from cte
)
--select all records where r=1 (i.e. the last created by the recursive cte, giving the largest range of start-to-end date for each id
select Id, PeriodStart, PeriodEnd
from cte2
where r = 1

Hopefully the comments explain what's going on; but if you need any clarifications, please comment.

The data set might have multiple periods like this. I basically want to turn that 4 record group into a 1 record with the Min Start Date and Max EndDate. — frmrock164, Oct 24 '18 at 18:12
This worked great. I just had to add logic for the REG_Number to separate the equipment periods, just incase two pieces of equipment had the same date/time. I posted the updated code just below as it wouldn't fit in a reply. — frmrock164, Oct 25 '18 at 14:31

score 0 · Accepted Answer · answered Oct 25 '18 at 14:32

with cte as
(
    --get all periods which don't immediately follow another period
    --these are the first record in the group (including groups of 1 record)
    --NB: assumes that a single record cannot have its PeriodStart = its own PeriodEnd
    select T1.ID, T1.START_TIME, T1.END_TIME, 1 Iteration,T1.REG_NUMBER 
    from 
        #tmpACTIVITIES T1
        LEFT JOIN #tmpACTIVITIES T2 ON (T1.REG_NUMBER=T2.REG_NUMBER) AND (T1.START_TIME=T2.END_TIME)
    WHERE 
        T2.ID IS NULL

    --where START_TIME not in (select END_TIME from #tmpACTIVITIES)

    union all

    --recursively get each period with a start date matching the last record's end date.
    --persist the original id and start date, use the new record's end date, add 1 to the iteration column each recursion
    select cte.ID, cte.START_TIME, mt.END_TIME, cte.Iteration + 1,cte.REG_NUMBER  
    from cte
    inner join #tmpACTIVITIES mt on (mt.REG_NUMBER=cte.REG_NUMBER) AND (mt.START_TIME = cte.END_TIME)
)
, cte2 as 
(
    --get all records / invert the Iteration (so the last record in a window has value r=1)
    select ID, START_TIME, END_TIME, REG_NUMBER ,row_number() over (partition by REG_NUMBER,ID order by Iteration desc) r
    from cte
)
--select all records where r=1 (i.e. the last created by the recursive cte, giving the largest range of start-to-end date for each id
select ID, START_TIME, END_TIME,REG_NUMBER 
from cte2
where r = 1

Grouping multiple periods into one period

2 Answers2

Update