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?

For Example:

  • SQL Server LIKE search with not matching as expected
  • Recommended approach to paginate 200 stored procedures using on ASP.NET MVC and SQL Server 2014
  • MSSQL - Pivot multi fields
  • Why does my trigger not fire?
  • Use stored procedure so that the output can be saved in a file or as an output parameter
  • How to change a stored procedure in VBA?
  • 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 lag and 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; 
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.