November 12th, 2003, 03:44 PM
Finding user with most calls by day.
So I have been working on this for 2 days now, and I am almost completly stuck.
I have a table Log_Main
Each record in the table represents a call that came into our office. Each record contains a Task_ID (Pri Key), a User_ID, a Call_Date, and other irrelivant data.
I need the query to count the number of calls each user has taken per day.. I got that part, here is the query for that.
SELECT Log_Main.User_ID, Log_Main.Call_Date, Count(*) AS Call_Count
GROUP BY Log_Main.User_ID, Log_Main.Call_Date
ORDER BY Log_Main.User_ID
now from this data I want to select a distinct date, the user with the most calls for that day, and the number of calls.
any help would be appreciated
thanks in advance.
November 14th, 2003, 11:13 AM
i would use a temp tables to get this done. It may be ugly but it should work...
Declare @temp table (user_id int, call_date datetime, call_count int)
insert into @temp
SELECT LM.User_ID, LM.Call_Date, Count(*) AS Call_Count
FROM Log_Main LM
GROUP BY LM.User_ID, LM.Call_Date
ORDER BY LM.User_ID
declare @temp2 table (call_date datetime, call_count int)
insert into @temp2
select call_date, max(call_count)
group by call_date
select t1.user_id, t1.call_date, t1.call_count
from @temp t1,
where t1.call_date = t2.call_date
and t1.call_count = t2.call_count