SQL How to get all repetitions within a certain period of time

Question

My table looks like

int callid not null,
datetime segstart not null,
varchar calling_pty not null

What I want is all lines with the same calling_pty that occur several times a day for 5 minutes from each other.

I am so shocked. I saw TOP 1 dated, and select the following and previous examples of strings, but I cannot work with this.

I am using MS SQL 2005.

Thank!

Answer 1

select t1.callid, t1.segstart, t1.calling_pty
from MyTable t1
inner join MyTable t2 on t1.calling_pty = t2.calling_pty 
    and t1.segstart < t2.segstart
where datediff(mi, t1.segstart, t2.segstart) <= 5 --a difference of 5 minutes 59 secs. still returns 5

Please note that since it DATEDIFFcounts the number of date boundaries that overlap, it can be somewhat approximate when counting minutes. For better accuracy you can use

where datediff(s, t1.segstart, t2.segstart) <= 300 --this looks at difference in seconds, so much nmore accurate