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
)

 select * from cte

Enter Parameters

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