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:
x
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;