Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JC-SQL
Starting Member
2 Posts |
 Posted - 2006-12-01 : 03:46:51
|
Hello,I am quite new in SQL, but always managed to find a way to get where I wanted to, but this time I can not think of a way to return what I want by myself, so am turning to experts.I have a database with two tables : One with the calls logs ("CallsLog"), listing each call made by each employee, telling which number has been called, how long the call was and how much it costed.I also have another table, "AddressBook", with all the phone numbers and names corresponding to these numbers, that I use in a left join on my query to return the names of the person called so it is more meaninful than just the phone numbers.Now, what I am trying to do is to return the ten persons that each employee called the most.I had no difficulty to get the total of time and money spent for callee by employee, but I can't think of a way to display only the first ten most called numbers by employee !Here is the query I came up with so far :SELECT *FROM ( SELECT CallerNumber, CallerName, CalledNumber, "CalleeName" = AddressBook.LastName + ' ' + Coalesce(AddressBook.FirstName, ''), "TotBilledCallDuration" = Cast(Sum(Cast(BilledCallDuration AS Float) - Floor(Cast(BilledCallDuration AS Float))) AS DateTime), "TotAmount" = sum(Amount) FROM CallsLog LEFT JOIN AddressBook ON CallsLog.CalledNumber = AddressBook.PhoneNumber WHERE CallType Like '%CALL%' GROUP BY CallerNumber, CallerName, CalledNumber, AddressBook.LastName + ' ' + Coalesce(AddressBook.FirstName, '') ) TORDER BY CallerNumber, TotBilledCallDuration DESC I have made a derived query, and have the feeling that I have to do something at the top of it, to return the 10 most called numbers by employee, but the question is what.TOP would just return me the first 10 most called people for just the first employee/caller returned by the query, and not for each employee/caller, and I am not sure how adding an auto-incremented identity column would help there.Can you please give me some pointers ? (unfortunately, I am not able to post example of the data due to the sensitive nature of the information).Any other advices on how to improve my query are very welcome.Thanks in advance, |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2006-12-01 : 13:01:33
|
| JC... did that do it for you or do you still need some help?--Jeff Moden |
 |
|
|
JC-SQL
Starting Member
2 Posts |
Posted - 2006-12-02 : 08:11:45
|
| Hi,Sorry for my late reply... I didn't have a chance to study it carefully yesterday.Thanks for the link (which contains other useful tricks as well)I think I understand how it works, but I wouldn't have had such a subtle idea myself as of now !Thanks for your help ! |
|
|
|
|
|
|
|
|
Return the 10 most called numbers foreach employee