I have a need to identify people that call in multiple times within a 3 hour period to different phone numbers within a company, but haven't figured out how to go about figuring out how to do the hours calculation/code for the recordset.
Table Fields (Smple Data):
ID SourceName Session Id From Name From Number To Name To Number Result Call Length Handle Time Call Start Time Call Direction Queue 387449 Daily_Calls_07_16_2021_07_17_28_AM.xlsx 618204818008
(201) 444-2233 Ryan (813) 555-6135 Answered 02:19 02:38 7/16/2021 13:39 Inbound
387565 Daily_Calls_07_16_2021_07_17_28_AM.xlsx 618176746018
(201) 444-2233 Ryan (813) 555-6135 Answered 01:15 01:30 7/16/2021 13:19 Inbound
377407 Daily_Calls_07_16_2021_07_43_29_AM.xlsx 363986948009
(201) 444-2309 John (813) 555-0555 Answered 01:33 01:26 7/15/2021 11:36 Inbound
381065 Daily_Calls_07_16_2021_11_01_27_AM.xlsx 363986948012
(201) 444-2309 Ryan (813) 555-6135 Answered 01:53 01:44 7/15/2021 11:41 Inbound
384723 Daily_Calls_07_16_2021_12_20_13_PM.xlsx 363986948019
(201) 444-2309 Bill (813) 555-0777 Answered 04:18 04:02 7/15/2021 11:55 Inbound
360079 Daily_Calls_07_16_2021_07_02_52_AM.xlsx 361126784009
(201) 444-6824 Anna (813) 555-1234 Answered 03:36 03:26 7/7/2021 12:54 Inbound
So for the attached example, I'm looking at the 'From Number' and identifying which ones are repeat callers, and placing them in the order they called. I then need to identify if they called multiple people, or just one contact. I'm only looking for phone numbers that called multiple people within a 3 hour period.
Where I'm geting stuck is the conversion from the query results above, to the query below. I know I need to loop through the recordsets, but not sure how to go about calculating the 3 hour time limit necessary.
For the example above, the results should be:
ID SourceName Session Id From Name From Number To Name To Number Result Call Length Handle Time Call Start Time Call Direction Queue 377407 Daily_Calls_07_16_2021_07_43_29_AM.xlsx 363986948009
(201) 444-2309 John (813) 555-0555 Answered 01:41 01:26 7/15/2021 11:36 Inbound
381065 Daily_Calls_07_16_2021_11_01_27_AM.xlsx 363986948012
(201) 444-2309 Ryan (813) 555-6135 Answered 01:53 01:44 7/15/2021 12:41 Inbound
384723 Daily_Calls_07_16_2021_12_20_13_PM.xlsx 363986948019
(201) 444-2309 Bill (813) 555-0777 Answered 04:18 04:02 7/15/2021 13:55 Inbound
I would like to see rows 3,4, and 5 as the result, as this caller had called 3 people in less than 3 hour period. This caller (201) 444-2309, called into 3 seperate people within the 3 hour period.
Any advice would be appreciated!
-Ryan
Answered