Group By records based on 10 second time interval in SQL server

Question

Requirement is to Group record of table based on 10 second time interval. Given table

 Id      DateTime            Rank
 1     2011-09-27 18:36:15      1
 2     2011-09-27 18:36:15      1
 3     2011-09-27 18:36:19      1
 4     2011-09-27 18:36:23      1
 5     2011-09-27 18:36:26      1
 6     2011-09-27 18:36:30      1
 7     2011-09-27 18:36:32      1
 8     2011-09-27 18:36:14      2
 9     2011-09-27 18:36:16      2
 10    2011-09-27 18:36:35      2

Group Should be like this

 Id      DateTime            Rank    GroupRank
 1     2011-09-27 18:36:15      1         1
 2     2011-09-27 18:36:15      1         1
 3     2011-09-27 18:36:19      1         1
 4     2011-09-27 18:36:23      1         1
 5     2011-09-27 18:36:26      1         2
 6     2011-09-27 18:36:30      1         2
 7     2011-09-27 18:36:32      1         2
 8     2011-09-27 18:36:14      2         3
 9     2011-09-27 18:36:16      2         3
 10    2011-09-27 18:36:35      2         4

For Rank 1 Minimum time is 18:36:15 and based on that all records between 18:36:15 to 18:36:24 should be in a group and so on.

I want GroupRank in the same table. so it would be something with dense_Rank() Over clause. Can anyone help me to write the query in SQL.

score 0 · Accepted Answer · answered Jul 19 '16 at 10:13

You need to do this in two steps, the first is to separate each record into its 10 second groups, by getting the number of seconds difference from the minimum time for each rank, dividing it by 10, then rounding it down to the nearest integer.

SELECT  *,
        SecondGroup = FLOOR(DATEDIFF(SECOND, 
                                    MIN([DateTime]) OVER(PARTITION BY [Rank]), 
                                    [DateTime]) / 10.0)
FROM    #T;

Which gives:

Id  DateTime                    Rank    SecondGroup
---------------------------------------------------
1   2011-09-27 18:36:15.000     1       0
2   2011-09-27 18:36:15.000     1       0
3   2011-09-27 18:36:19.000     1       0
4   2011-09-27 18:36:23.000     1       0
5   2011-09-27 18:36:26.000     1       1
6   2011-09-27 18:36:30.000     1       1
7   2011-09-27 18:36:32.000     1       1
8   2011-09-27 18:36:14.000     2       0
9   2011-09-27 18:36:16.000     2       0
10  2011-09-27 18:36:35.000     2       2

Then you can do your DENSE_RANK ordering by Rank and SecondGroup:

SELECT  Id, [DateTime], [Rank],
        GroupRank = DENSE_RANK() OVER(ORDER BY [Rank], SecondGroup)
FROM    (   SELECT  *,
                    SecondGroup = FLOOR(DATEDIFF(SECOND, 
                                                MIN([DateTime]) OVER(PARTITION BY [Rank]), 
                                                [DateTime]) / 10.0)
            FROM    #T
        ) AS t;

Which gives your desired output.

SAMPLE DATA

CREATE TABLE #T (Id INT, [DateTime] DATETIME, [Rank] INT);

INSERT #T (Id, [DateTime], [Rank])
VALUES
    (1, '2011-09-27 18:36:15', 1),
    (2, '2011-09-27 18:36:15', 1),
    (3, '2011-09-27 18:36:19', 1),
    (4, '2011-09-27 18:36:23', 1),
    (5, '2011-09-27 18:36:26', 1),
    (6, '2011-09-27 18:36:30', 1),
    (7, '2011-09-27 18:36:32', 1),
    (8, '2011-09-27 18:36:14', 2),
    (9, '2011-09-27 18:36:16', 2),
    (10, '2011-09-27 18:36:35', 2);

Group By records based on 10 second time interval in SQL server

1 Answers1

Linked