#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    3
    Rep Power
    0

    Angry 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
    FROM Log_Main
    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.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    21
    Rep Power
    0
    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)
    from @temp
    group by call_date


    select t1.user_id, t1.call_date, t1.call_count
    from @temp t1,
    @temp2 t2
    where t1.call_date = t2.call_date
    and t1.call_count = t2.call_count

IMN logo majestic logo threadwatch logo seochat tools logo