– Table var to store the gaps
declare @T table
(
DeviceID varchar(10),
PrevPeriodEnd datetime,
NextPeriodStart datetime
)
-- Get the gaps;with cte as ( select *, row_number() over(partition by DeviceID order by Timestamp) as rn from data)insert into @Tselect C1.DeviceID, C1.Timestamp as PrevPeriodEnd, C2.Timestamp as NextPeriodStartfrom cte as C1 inner join cte as C2 on C1.rn = C2.rn-1 and C1.DeviceID = C2.DeviceID and datediff(s, C1.Timestamp, C2.Timestamp) > 5-- Build islands from gaps in @T;with cte1 as( -- Add first and last timestamp to gaps select DeviceID, PrevPeriodEnd, NextPeriodStart from @T union all select DeviceID, max(TimeStamp) as PrevPeriodEnd, null as NextPeriodStart from data group by DeviceID union all select DeviceID, null as PrevPeriodEnd, min(TimeStamp) as PrevPeriodEnd from data group by DeviceID),cte2 as( select *, row_number() over(partition by DeviceID order by PrevPeriodEnd) as rn from cte1)select C1.DeviceID, C1.NextPeriodStart as PeriodStart, C2.PrevPeriodEnd as PeriodEndfrom cte2 as C1 inner join cte2 as C2 on C1.DeviceID = C2.DeviceID and C1.rn = C2.rn-1order by C1.DeviceID, C1.NextPeriodStart
