declare @data table ( DeviceID int, Timestamp datetime ) ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Open Source Meta

Q&A about the site for people organizing, marketing or licensing open source development projects

declare @data table
(
  DeviceID int,
  Timestamp datetime
)

declare @T table
(
  DeviceID int,
  PrevPeriodEnd datetime,
  NextPeriodStart datetime
) 

insert into @data values
(1,'1 Jan 2012 00:00:00'),
(1,'1 Jan 2012 00:00:30'),
(1,'1 Jan 2012 00:01:00'),
(1,'1 Jan 2012 00:01:30'),
(1,'1 Jan 2012 00:02:15'),
(1,'1 Jan 2012 00:07:30'),
(1,'1 Jan 2012 00:07:59'),
(1,'1 Jan 2012 00:15:30'),
(2,'1 Jan 2012 00:15:59'),
(2,'1 Jan 2012 00:16:30'),
(2,'1 Jan 2012 00:16:59'),
(2,'1 Jan 2012 00:17:30'),
(2,'1 Jan 2012 00:17:59')


-- Get the gaps


-- Get the gaps
;with cte as 
(
  select *,
    row_number() over(partition by DeviceID order by Timestamp) as rn
  from @data
)
insert into @T
select
  C1.DeviceID,
  C1.Timestamp as PrevPeriodEnd,
  C2.Timestamp as NextPeriodStart
from 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 PeriodEnd
from cte2 as C1
  inner join cte2 as C2
    on C1.DeviceID = C2.DeviceID and
       C1.rn = C2.rn-1
order by C1.DeviceID, C1.NextPeriodStart

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results