menhealthmag.co.uk

Health Magazine for Men

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

Leave a Reply

Your email address will not be published.