I am trying to write a query to retrieve the IDs from a table which looks like this:
| ID | TYPE | CREATED_TIME |
|---|---|---|
| 1234 | start | 2021-11-01 21:43:48.0000000 |
| 1234 | start | 2021-11-01 21:44:40.0000000 |
| 1234 | end | 2021-11-04 15:27:50.0000000 |
| 4567 | start | 2021-09-02 20:12:40.0000000 |
| 4567 | start | 2021-09-02 23:01:11.0000000 |
Ideally I want the query to return the ID's which have 2 or more records of the same type and were created less than 3 minutes apart. So it should return ID 1234 because it has the 2 records of the type = start and created time less than 1 minute apart. It should not return 4567 because the createdtime is 3 hours apart.
CodePudding user response：
Assuming your table is called DATA this should work:
SELECT DISTINCT t1.ID
FROM
DATA t1 JOIN
DATA t2 ON t1.ID = t2.ID AND t1.TYPE = t2.TYPE AND t1.CREATED_TIME <> t2.CREATED_TIME AND (ABS(DATEDIFF(MINUTE, t1.CREATED_TIME, t2.CREATED_TIME)) < 3)
CodePudding user response：
Self-joining is inefficient. You should use window functions for this
SELECT ID
FROM (
SELECT *,
PrevVal = LAG(CREATED_TIME, 1, '19000101') OVER (PARTITION BY ID, Type ORDER BY CREATED_TIME)
FROM YourTable t
) t
WHERE DATEADD(minute, 3, t.PrevVal) >= t.CREATED_TIME
GROUP BY ID;