CODEWITHSUNDEEP
sqldatabasesql-server-2005
CodingIsAwesome
CodingIsAwesome

Reputation: 1966

SQL How to get all the repeats within a specified timeframe

My table looks likes

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

What I want to get is all rows with the same calling_pty that occur more than once per day within 5 minutes of each other.

I'm so stumpted. I've seen the TOP 1, datediff, and select next and previous row examples but I can't work this one out.

I'm using MS SQL 2005.

Thank you!

Upvotes: 3

Views: 195

Answers (1)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

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

Note that since DATEDIFF counts number of date boundaries crossed, it can be somewhat approximate when counting minutes. For better accuracy, you may wish to use

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

Upvotes: 4

Related Questions