Post reply

Query for average call time spent for user in SSRS

  • July 5, 2011 at 5:53 am

    #253205

    I got some help last time with how to get the average fix time for all calls across a team and have the below query:

    select cast(AvgMins/60 as varchar(10)) + ':' + right('00' + cast(AvgMins%60 as varchar(10)), 2)

    from

    (Select sum(fix_time/60)/COUNT (*) as AvgMins

    from opencall

    where suppgroup = 'WEBS'and date_time between (@StartDate) and (@EndDate)) as Sub1;

    I now need to do this breakdown per user, the field name is owner, but I am struggling where on earth to put this in the above.

    Can anyone help? I think I have been looking at things for too long again! Thank you.....

  • July 5, 2011 at 6:56 am

    #1348245

    This should work:

    select [User], cast(AvgMins/60 as varchar(10)) + ':' + right('00' + cast(AvgMins%60 as varchar(10)), 2)

    from

    (Select owner as , sum(fix_time/60)/COUNT (*) as AvgMins

    from opencall

    where suppgroup = 'WEBS'and date_time between (@StartDate) and (@EndDate)

    group by owner) as Sub1;

    I have added the owner field to the subquery and given it an alias of User, and then grouped by this field so the subquery will return one line per user, which are then formatted by the outer query

  • July 5, 2011 at 9:58 am

    #1348391

    Thank you so much for this, it worked perfectly!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply