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
;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(m, C1.Timestamp, C2.Timestamp) > 6
       
       select * from @T

Enter Parameters

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