cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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

2 REPLIES 2
Anonymous
Not applicable

Yalan Wu,

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
Community Support

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;

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.