T-SQL Finding person with most calls and to who he has called [duplicate]
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