Skip to content
Advertisement

Find repeating phone numbers between a 7 day range

I have a phone and a call date field. I need to find all phone and call dates where calls were made more than once (>1) within a 7 day period.

What is the best approach to this?

Example:

ID|Phone|CallDate
-----------------
1|5551212|11/21/2020
2|5551212|11/22/2020
3|5551212|10/9/2020 
4|4441212|11/22/2020
5|4441212|11/1/2020 

output:

5551212|11/21/2020
5551212|11/22/2020

Here’s an example query I tried but I assume I can do better (besides, its taking a very long time, over 1 million records):

  SELECT A1.Phone
  FROM CallDetail A1, CallDetail A2
  WHERE (A1.Phone = A2.Phone) AND (A1.ID <> A2.ID)
  GROUP BY A1.Phone, A1.CallDate, A2.CallDate 
  HAVING COUNT(A1.Phone) > 1 AND DATEDIFF(DAY, A1.CallDate, A2.CallDate) <= 7

Advertisement

Answer

You seem to want lead() and lag() to compare the calldate on one row to the nearest calldate before or after:

select cd.*
from (select cd.*
             lag(cd.calldate) over (partition by cd.phone order by cd.calldate) as prev_calldate,
             lead(cd.calldate) over (partition by cd.phone order by cd.calldate) as next_calldate
      from calldetail cd
     ) cd
where prev_calldate > dateadd(day, -7, calldate) or
      next_calldate < dateadd(day, 7, calldate)
order by phone, calldate;
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement