从日期时间表中获取包含开始和结束值的列表

从日期时间表中获取包含开始和结束值的列表

– 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