CODEWITHSUNDEEP
sql-server
Willem Hamelink
Willem Hamelink

Reputation: 13

How can I Filter all records where same id's Time differ a few seconds sql server

This is the select statement to get data between certain dates:

SELECT DISTINCT Events.Clock,Events.TagID       
FROM Events
WHERE (DATEDIFF(SECOND, Events.Clock, '2013/04/20') <= 30) AND (DATEDIFF(SECOND,                     Events.Clock, '2013/04/21') >= 30) ORDER BY Events.TagID DESC

This is the results I get from the statement.

     | Clock               |  TagID        | 
---------------------------------------------
1    | 2013-04-20 12:39:18 | 4CB0000060032 | 
2    | 2013-04-20 12:39:16 | 4CB0000060032 | 
3    | 2013-04-20 16:53:09 | 4CB0000060032 | 
4    | 2013-04-20 13:22:38 | 4CB00000600EF | 
5    | 2013-04-20 13:22:40 | 4CB00000600EF | 
6    | 2013-04-20 15:20:56 | 4CB00000600D2 | 
7    | 2013-04-20 15:17:31 | 4CB00000600D2 | 
8    | 2013-04-20 15:20:58 | 4CB00000600D2 | 
9    | 2013-04-20 19:33:09 | 4CB00000600D1 | 
10   | 2013-04-20 20:39:16 | 4CB00000600D1 | 
11   | 2013-04-20 11:10:38 | 4CB00000600D1 |

Now I would like to filter the results more because as you can see there are records that the time differ in milliseconds and others in seconds, I would like to get rid of the records with the same TagID and the time that follows a few seconds/milliseconds. Duplicates of a TagID is fine just not where the times are a few seconds/milliseconds apart from another.

Thus record 2, 4, 6, 7 will not be there.

Then the results I would like to get will be the following:

    | Clock               |  TagID        | 
-------------------------------------------
1   | 2013-04-20 12:39:18 | 4CB0000060032 | 
2   | 2013-04-20 16:53:09 | 4CB0000060032 | 
3   | 2013-04-20 13:22:40 | 4CB00000600EF |
4   | 2013-04-20 15:20:58 | 4CB00000600D2 | 
5   | 2013-04-20 19:33:09 | 4CB00000600D1 | 
6   | 2013-04-20 20:39:16 | 4CB00000600D1 |
7   | 2013-04-20 11:10:38 | 4CB00000600D1 |

Upvotes: 0

Views: 354

Answers (1)

NigelK
NigelK

Reputation: 8490

Create a temp table to hold the resuls from your current query:

CREATE TABLE #Events (
Clock DATETIME,
TagID VARCHAR(20)
)

Your current query:

INSERT #Events
SELECT DISTINCT Events.Clock,Events.TagID       
FROM Events
WHERE (DATEDIFF(SECOND, Events.Clock, '2013/04/20') <= 30)
 AND (DATEDIFF(SECOND, Events.Clock, '2013/04/21') >= 30)
ORDER BY Events.TagID DESC

Then select only those rows where there is no earlier dated row (for the same TagID) within a certain timeframe. I've used 3000 milliseconds here as an example.

SELECT * FROM #Events e1
WHERE NOT EXISTS
(SELECT 1 FROM #Events e2
 WHERE e2.TagID = e1.TagID
   AND e2.Clock < e1.Clock
   AND DATEDIFF(millisecond, e2.Clock, e1.Clock) < 3000)

Upvotes: 1

Related Questions