SQL simple datetime count occurences query driving me crazy!

Thank you to anyone that can help. I am using MSSQL 2005

I have a database like so

SegmentDT DateTime,
WhoCalled varchar(4)

All I want to do is find all of the WhoCalled that 开发者_运维问答have a count of calls within 5 minutes of each other.

How can I accomplish this?

• You may not need the ABS
• A 5 minute DATEDIFF covers the range 4 mins and 0.003 seconds to 5 mins and 59.997 seconds because of how boundaries are done (zero seconds)
• COUNT(DISTINCT SegmentDT) should remove occurences where you have several calls in a 300 second window

So:

SELECT
     WhoCalled, COUNT(DISTINCT SegmentDT)
FROM
     MyTable a
     INNER JOIN
     MyTable b ON a.WhoCalled = b.WhoCalled
WHERE
     ABS(DateDiff(second, a.SegmentDT, b.SegmentDT)) <= 300

Another formulation is to use a window function. This will compare adjacent rows:

;WITH CTE AS
(
    SELECT
        WhoCalled, 
        ROW_NUMBER() OVER (ORDER BY SegmentDT) AS RowNum
    FROM
        MyTable
)
SELECT
     WhoCalled, COUNT(*)
FROM
     CTE a
     INNER JOIN
     CTE b ON a.WhoCalled = b.WhoCalled AND a.RowNum + 1 = b.RowNum
WHERE
     DateDiff(second, a.SegmentDT, b.SegmentDT) <= 300

SELECT WhoCalled, count(*)
FROM tbl a
INNER JOIN tbl b ON a.WhoCalled = b.WhoCalled
AND DateDiff(n, a.SegmentDT, b.SegmentDT) > 0
AND DateDiff(n, a.SegmentDT, b.SegmentDT) <= 5