SQL: Filter rows where event of type A happened without event of type B happening within 1 second of event of type A

user_5957555 0
Viewed 44

let's assume I have a Table that looks like this:

timestamp                    event       
2021-06-01 12:00:00.000      A          
2021-06-01 12:00:00.200      B          
2021-06-01 12:00:00.500      C          
2021-06-01 12:00:01.700      A
2021-06-01 12:00:02.000      D
2021-06-01 12:00:03.000      B

I want to select timestamps of all events A where event B and C has not happened within 1 second of event A.

So from the example above, timestamp 2021-06-01 12:00:01.700 would be returned.

Preferably in SQL Server, but any SQL syntax will do to help me figure out how to write such query.

tsql sql-server sql
1 Answers

We can use exists logic here:

SELECT timestamp, event
FROM yourTable t1
WHERE event = 'A' AND
      NOT EXISTS (
          SELECT 1
          FROM yourTable t2
          WHERE ABS(DATEDIFF(ms, t2.timestamp, t1.timestamp)) <= 1000 AND
                t2.event IN ('B', 'C')
      );

Demo

Read in plain English, the above query says to return any record which is an A event, for which we cannot find a B or C event record which is within 1 second or less (either before or after) of the A record.

user_18632290
Related