Jump to content

Call resolution record widget


James Bartle
 Share

Recommended Posts

Hi, I was wondering if there was anyway to set up a call resolution record widget.

By this I mean having a widget that displays the top 5 people who have the resolved the highest amount of calls in a 7 day period. But showing this forever.

E.G.

John Smith has resolved 84 calls in a week at some point and Keith has resolved 76 calls in one week. These top 2 places will be displayed on a widget as these values forever (Unless they manage to beat that amount)

We like to have a bit of friendly competiton in the office so this would make it all the better.

Thanks.

Link to comment
Share on other sites

  • 2 weeks later...

Something to get you started, here's a query that will group the requests resolved by a user by week (year also taken into account), ordered by the most resolved

SELECT
	h_resolvedby_user_id,
	COUNT(h_pk_reference) as resolved_count,
	CONCAT(YEAR(h_datelogged), '/', WEEK(h_datelogged)) as year_week
FROM
	h_itsm_requests
WHERE
	h_resolvedby_user_id IS NOT NULL
GROUP BY
	h_resolvedby_user_id, year_week
ORDER BY
	resolved_count DESC

 

Link to comment
Share on other sites

  • 2 weeks later...
On 29/09/2017 at 10:56 AM, Chaz said:

Something to get you started, here's a query that will group the requests resolved by a user by week (year also taken into account), ordered by the most resolved


SELECT
	h_resolvedby_user_id,
	COUNT(h_pk_reference) as resolved_count,
	CONCAT(YEAR(h_datelogged), '/', WEEK(h_datelogged)) as year_week
FROM
	h_itsm_requests
WHERE
	h_resolvedby_user_id IS NOT NULL
GROUP BY
	h_resolvedby_user_id, year_week
ORDER BY
	resolved_count DESC

 

Hi Chaz,

How exactly would that be set up sorry?

I tried pasting it into the filter to no avail. The image attached is the way the options are set up. Would you be able to say how they should be in there at all?

Thanks

Capture.PNG

Link to comment
Share on other sites

Small changes to the SQL if you want to focus on properly resolved calls, and limit to TOP 5

SELECT
	h_resolvedby_user_id,
	COUNT(h_pk_reference) as resolved_count,
	CONCAT(YEAR(h_dateresolved), '/', WEEK(h_dateresolved)) as year_week
FROM
	h_itsm_requests
WHERE
	h_resolvedby_user_id IS NOT NULL and h_dateresolved IS NOT NULL and h_reopencount = 0
GROUP BY
	h_resolvedby_user_id, year_week
ORDER BY
	resolved_count DESC
LIMIT 5

(includes a filter on calls resolved but not having been reopened!)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...