SQL Server concurrent 5 timestamps in a columns
I would like to get an SQL Query that would help me to fetch the times in a row whichever lies with in 5 minutes and the count of times noticed should greater than or equal to 5?
Column A 17:22:23 17:44:31 17:25:36 17:24:11 17:47:39 17:26:22 17:22:44
My results should fetch the below values only?
17:22:23 17:25:36 17:24:11 17:26:22 17:22:44
2 Solutions collect form web for “SQL Server concurrent 5 timestamps in a columns”
the exact ask is to fetch something that lies 5 mins between each
other and has repeated more than 5 times
This should do it
WITH T1 AS (SELECT *, KeepPreceding = IIF(DATEDIFF(MINUTE, LAG(ColumnA, 4) OVER (ORDER BY ColumnA), ColumnA) <= +5, 1, 0), KeepFollowing = IIF(DATEDIFF(MINUTE, LEAD(ColumnA, 4) OVER (ORDER BY ColumnA), ColumnA) >= -5, 1, 0) FROM YourTable), T2 AS (SELECT *, /*If at least one of the 4 following rows or this one has the KeepPreceding flag then preserve this row*/ MAX(KeepPreceding) OVER (ORDER BY ColumnA ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) AS KeptForPreceding, /*If at least one of the 4 preceding rows or this one has the KeepFollowing flag then preserve this row*/ MAX(KeepFollowing) OVER (ORDER BY ColumnA ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS KeptForFollowing FROM T1) SELECT ColumnA FROM T2 WHERE 1 IN ( KeptForPreceding, KeptForFollowing ) ORDER BY ColumnA;
You can use
lead as below:
SELECT [ColumnA] FROM (SELECT [ColumnA], PrevVal = LAG([ColumnA]) OVER (ORDER BY [ColumnA]), NextVal = LEAD([ColumnA]) OVER (ORDER BY [ColumnA]) FROM YourTable) a WHERE DATEDIFF(MINUTE, PrevVal, [ColumnA]) <= 5 OR DATEDIFF(MINUTE, [ColumnA], NextVal) <= 5;