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;