How do I group records having time difference of more than an hour?

group-by, sql, sql-server

Solution

You cannot do this with a simple `GROUP BY`. This operator only works on single columns - e.g. you could group by computer name or something, but you cannot add additional logic like difference in time must be greater than one hour or anything like that to the grouping.

What you can do - provided you're on SQL Server 2005 or newer (you didn't mention the version in your question) would be to use CTE's (Common Table Expressions). Those provide a way to slice'n'dice your data.

Here, I'm doing several things - first I'm "partitioning" the data by `ComputerName` and order by `VisitDate` and using `ROW_NUMBER()` to get a sequential number for each partition. Then the second CTE determines the "first" entry for each computer - the one with row number = 1 - and the third finally determines the difference in the `VisitDate` for each entry, compared to the entry with row number = 1. From that third CTE, I finally select those entries that either have row number = 1 (the first for each "partition"), or anything that has a difference in minutes of 60 or more.

Here's the code:

;WITH Computers AS
(
    SELECT
        ComputerName, VisitDate,
        RN = ROW_NUMBER() OVER(PARTITION BY ComputerName ORDER BY VisitDate)
    FROM    
        dbo.YourComputerTable
),
FirstComputers AS
(
    SELECT ComputerName, VisitDate
    FROM Computers
    WHERE RN = 1
),
SelectedComputers AS
(
    SELECT 
        c.ComputerName, c.VisitDate, c.RN,
        DiffToFirst = ABS(DATEDIFF(MINUTE, c.VisitDate, fc.VisitDate))
    FROM Computers c
    INNER JOIN FirstComputers fc ON c.ComputerName = fc.ComputerName
)
SELECT * 
FROM SelectedComputers
WHERE RN = 1 OR DiffToFirst >= 60

Problem

Original source