CODEWITHSUNDEEP
sqlsql-server
David Kethel
David Kethel

Reputation: 2550

How to find records that are more than 5 mins after the record before it in sql

Given a table with a "recorded" date time field.

How can I select all the records that have a recorded date/time that is more than 5 mins after the record before it when the table is sorted by recorded. Or to put it another way, where are the gaps between records that are greater then 5 min.

At the moment I am doing this in C# by iterating over the set and comparing record n to record n+ 1.

Is it possible to do this with sql, preferably with out using a cursor?

Upvotes: 0

Views: 168

Answers (1)

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36621

How about this with CTE approach.

WITH mrows AS
(
     SELECT  *, ROW_NUMBER() OVER (ORDER BY recorded) AS rn
     FROM    Table_name
)


SELECT * 
FROM   mrows mr1 INNER JOIN mrows mr2
ON     mr1.rn = mr2.rn - 1
WHERE  datediff(mm, mr1.recorded, mr2.recorded) = 5;

Upvotes: 1

Related Questions