Count of phone number in table between two dates in the same table

[Anonymous]

I have a table that consists of the columns below

Call Date and Time    |    Phone Number   |

1-1-22 10:15:36               123-456-7890

1-1-22-10:18:23               256-435-9087

etc

etc

etc

The table spans millions of phone numbers and thousands of days.  I need to calculate the number of times each phone number appears in the table if the phone number calls back within 24 hours of the Call Data and Time.  

Any help would be terrific.  THANKS

[Anonymous]

Yalan Wu, 

Thank you for your response.  

I basically need to calculae the total number of times that a number repeats with the 48 hours after the original call date time.  

In the example below, the first instance is on 6-2-21 at 8:42 AM. There are three more instances within 48 hours giving us a total of 4. The second example occurs on 6-21-21 at 8:53 AM. There are two more occurances of this number after that date/time within the 48 hour window giving us a count of 3. In the next example, there is only 1 more occurance after the call date/time giving a count of 2, and finally in the last example, there is only 1 instance within the 48 hour window, giving a count of 1.  The instances are in RED

This metric only needs to look forward, not backward.  

CallDateTimeLocal	RemotePhoneNumber	Number of calls within 48 Hours
6/2/21 6:54 AM	401333399	1
6/2/21 7:07 AM	401680604	1
6/2/21 7:14 AM	401414790	1
6/2/21 7:18 AM	401771323	1
6/2/21 7:24 AM	401309603	1
6/2/21 7:36 AM	401787433	1
6/2/21 8:05 AM	401365889	1
6/2/21 8:15 AM	401272061	1
6/2/21 8:16 AM	401390017	1
6/2/21 8:25 AM	401298671	1
6/2/21 8:31 AM	401301390	1
6/2/21 8:31 AM	401659723	1
6/2/21 8:35 AM	617579664	1
6/2/21 8:42 AM	401444687	4
6/2/21 8:53 AM	401444687	3
6/2/21 8:58 AM	401749763	1
6/2/21 9:04 AM	401273109	1
6/2/21 9:14 AM	401545089	1
6/3/21 8:08 AM	508973780	1
6/3/21 8:09 AM	401444687	2
6/3/21 8:09 AM	401470969	1
6/3/21 8:15 AM	401437657	1
6/3/21 8:16 AM	401481249	1
6/3/21 8:17 AM	401442885	1
6/3/21 8:22 AM	401871357	1
6/3/21 8:31 AM	401480136	1
6/3/21 8:44 AM	508336092	1
6/3/21 8:58 AM	401265020	1
6/3/21 9:13 AM	401444687	1

[v-yalanwu-msft]

Hi, @Anonymous ;

Can you provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples.? I am not very clear for 24 hours according to the earliest time of each number or the daily 0:00-24:00 this time period;

Looking forward to your reply！

 
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.