T-SQL Finding person with most calls and to who he has called

Question

My goal is to find a person who had the longest call displaying call time, phone number and person name, last name.

Everything has to be done in one query.

The part of which I do not understand how to approach is, I'm able to find the longest call by SubscriberID, but I cannot show which phone number is it exactly, because if I do max() and group SubscriberID with AdresseeNumber, I get all numbers and all calls.

I have three tables.

NetworkP2P, Subscriber, and Person.

NetworkP2P has the following data as an example:

NetworkP2P
SubscriberID | ServiceID | AddresseeNumber | CallStart               | CallEnd
1                  1         613-555-0160    2014-08-02 08:30:24.000   2014-08-02 08:45:23.000           
1                  2         614-545-0130    2014-08-02 08:30:24.000   2014-08-02 08:30:24.000
1                  2         653-535-0120    2014-08-02 08:30:24.000   2014-08-02 08:30:24.000
2                  2         653-563-0312    2014-08-02 08:30:24.000   2014-08-02 08:30:24.000
2                  2         613-645-0160    2014-08-02 08:30:24.000   2014-08-02 08:45:24.000
3                  2         613-812-0160    2014-08-02 08:30:24.000   2014-08-02 08:45:24.000
3                  2         613-958-0160    2014-08-02 08:30:24.000   2014-08-02 08:45:24.000
4                  1         613-492-0160    2014-08-02 08:30:24.000   2014-08-02 08:45:24.000



Subscriber
SubscriberID | PersonID
1                 1
2                 1
3                 2
4                 3



Person
PersonID | Name | LastName
1          John    Michael
2          Adam    Savage
3          George   Good

This query will display all calls and sort by a maximum of all calls. How would I approach firstly adding the phone number to the already selected ones? Furthermore, after that, how do I specify the subscriber ID by its name and last name?

SELECT P2P.SubscriberID , max(DATEDIFF(minute, CallStart, CallEnd)) as 'TimeCall'
FROM dbo.NetworkP2P AS P2P
WHERE ServiceID IN(1,2)
GROUP BY p2p.SubscriberID
ORDER BY P2P.SubscriberID ASC

*"I cannot show which phone number is it exactly"* Does this answer your question [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Thom A, Dec 23 '21 at 17:18
Unfortunately, going through, I do not find how it can help to my issue. — Marius, Dec 23 '21 at 17:31
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question, no images. — Yitzhak Khabinsky, Dec 23 '21 at 17:34

score 0 · Accepted Answer · answered Dec 23 '21 at 17:54

You can use window functions like row_number() to rank the rows based on call-time and then pick the first record for each subscriber. Here is an example

;WITH CTE AS
(
  SELECT P2P.SubscriberID , 
         P2P.AddresseeNumber,
         P.Name,
         P.LastName,
         DATEDIFF(minute, P2P.CallStart, P2P.CallEnd) AS TimeCall,
         ROW_NUMBER() OVER(PARTITION BY P2P.SubscriberID
                          ORDER BY DATEDIFF(minute, P2P.CallStart, P2P.CallEnd) DESC) AS RN 
  FROM dbo.NetworkP2P AS P2P
  JOIN Subscriber S
    ON P2P.SubscriberID = S.SubscriberID
  JOIN Person P
    ON S.PersonID = P.PersonID
  WHERE P2P.ServiceID IN(1,2)
)
SELECT SubscriberID , 
       AddresseeNumber,
       Name,
       LastName,
       TimeCall
FROM CTE
WHERE RN = 1
ORDER BY SubscriberID

score 0 · Answer 2 · answered Dec 23 '21 at 19:01

This would give you the longest call by Subscriber

SELECT P2P.SubscriberID , P.Name, P.LastName, P2P.AddresseeNumber, DATEDIFF(minute, P2P.CallStart, P2P.CallEnd) as 'TimeCall'
FROM dbo.NetworkP2P AS P2P
JOIN dbo.Subscriber AS S ON P2P.SubscriberID=S.SubscriberID
JOIN dbo.Person AS P ON S.PersonID=P.PersonID
WHERE P2P.ServiceID IN(1,2) 
AND DATEDIFF(minute, P2P.CallStart, P2P.CallEnd)=(SELECT max(DATEDIFF(minute, P2P2.CallStart, P2P2.CallEnd)) 
    FROM dbo.NetworkP2P AS P2P2)
GROUP BY p2p.SubscriberID
ORDER BY P2P.SubscriberID ASC

This would give you the longest calls of all subscribers matching the longest call length.

SELECT P2P.SubscriberID , P.Name, P.LastName, P2P.AddresseeNumber, DATEDIFF(minute, CallStart, CallEnd) as 'TimeCall'
FROM dbo.NetworkP2P AS P2P
JOIN dbo.Subscriber AS S ON P2P.SubscriberID=S.SubscriberID
JOIN dbo.Person AS P ON S.PersonID=P.PersonID
WHERE P2P.ServiceID IN(1,2) 
AND DATEDIFF(minute, P2P.CallStart, P2P.CallEnd)=(SELECT max(DATEDIFF(minute, P2P2.CallStart, P2P2.CallEnd)) 
    FROM dbo.NetworkP2P AS P2P2)

If you want to guarantee that you only get one record add LIMIT and you will get the first longest record if more than 1 are the same length

SELECT P2P.SubscriberID , P.Name, P.LastName, P2P.AddresseeNumber, DATEDIFF(minute, CallStart, CallEnd) as 'TimeCall'
FROM dbo.NetworkP2P AS P2P
JOIN dbo.Subscriber AS S ON P2P.SubscriberID=S.SubscriberID
JOIN dbo.Person AS P ON S.PersonID=P.PersonID
WHERE P2P.ServiceID IN(1,2) 
AND DATEDIFF(minute, P2P.CallStart, P2P.CallEnd)=(SELECT max(DATEDIFF(minute, P2P2.CallStart, P2P2.CallEnd)) 
    FROM dbo.NetworkP2P AS P2P2)
LIMIT 1

Thank you, I'm having an issue running the first one, as it requires me to add all of the Select statements to the GROUP BY, Is there any way to overcome it? — Marius, Dec 23 '21 at 19:13

T-SQL Finding person with most calls and to who he has called

2 Answers2