CODEWITHSUNDEEP
sql-server
Bob Lotz
Bob Lotz

Reputation: 85

Grouping rows by time span

Need to count number of times a pair of objects appear together within a given time interval. The catch is, if same object is listed multiple times within 5 minute span that should count as one occurrence. I have the code to count each individual pair occurrences but can't figure out how to group multiple occurrences appearing within 5 minutes:

if object_id(N'a', N'U') IS NOT NULL
    DROP TABLE a;
GO 

create table a (name varchar (20), dt datetime);
go

create index i1 on a (name);

insert a 
values 
  ('A', '20140101 13:00:00.000')
, ('A', '20140101 13:00:01.000')
, ('A', '20140101 13:00:02.000')
, ('B', '20140101 13:01:00.000')
, ('C', '20140101 13:01:30.000')
, ('D', '20140101 13:02:00.000')
, ('E', '20140101 13:02:30.000')

, ('B', '20140201 13:00:00.000')
, ('C', '20140201 13:01:00.000')
, ('K', '20140201 13:01:30.000')
, ('L', '20140201 13:02:00.000')
, ('M', '20140201 13:02:30.000')
, ('A', '20140201 13:03:00.000')

, ('A', '20140301 13:00:00.000')
, ('D', '20140301 13:01:00.000')
, ('E', '20140301 13:01:30.000')
, ('P', '20140301 13:02:00.000')
, ('R', '20140301 13:02:30.000')
, ('Q', '20140301 13:03:00.000')

, ('A', '20140401 13:00:00.000')
, ('X', '20140401 13:01:00.000')
, ('Y', '20140401 13:01:30.000')
, ('Z', '20140401 13:02:00.000')
GO

with prox (FirstName, SecondName)
    AS (select a.name, b.name
        from a
        cross join a AS b
        where a.name < b.name
        and ABS(DATEDIFF(mi, a.dt, b.dt)) < 5)

select  FirstName, SecondName, count(*)
from    prox
group by FirstName, SecondName
having count(*) > 1
order by 1, 2

In my example the count for AB, AC, AD and AE should be 2 not 4 because in the first group A appeared 3 times within 5 minutes which should count once not 3 times.

Upvotes: 2

Views: 121

Answers (1)

JamieD77
JamieD77

Reputation: 13949

you can create another cte to only get values that are at least 5 minutes are apart first then use your cte

WITH    cte
    AS (SELECT
            name,
            dt
        FROM
            a a1
        WHERE
            dt = (SELECT MIN (dt) FROM a a2 WHERE a2.name = a1.name
                )
        UNION ALL
        SELECT
            a1.name,
            a1.dt
        FROM
            a a1
        JOIN cte a2 ON a1.name = a2.name
                        AND a1.dt > DATEADD(minute,5,a2.dt)
        ),
cteGroup 
    AS (SELECT DISTINCT
            name,
            dt
        FROM 
            cte
    ),  

prox(FirstName,SecondName)
    AS (SELECT
            a.name,
            b.name
        FROM
            cteGroup AS a
        CROSS JOIN cteGroup AS b
        WHERE
            a.name < b.name
            AND ABS(DATEDIFF(mi,a.dt,b.dt)) < 5
        )
SELECT
    FirstName,
    SecondName,
    COUNT(*)
FROM
    prox
GROUP BY
    FirstName,
    SecondName
HAVING
    COUNT(*) > 1
ORDER BY
    1,
    2

SQL Fiddle

Upvotes: 1

Related Questions