Jump to content
James Bartle

Call resolution record widget

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.

Share this post


Link to post
Share on other sites

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

 

  • Confused 1

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

@James Bartle If you change the type of widget to 'List of Data', you'll then have the option to provide a custom SQL query

8hMSEwT.png

 

  • Thanks 1

Share this post


Link to post
Share on other sites

Hi Chaz,

Following that what specific things would I need to change in order to have the chart show up?

Share this post


Link to post
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!)

Share this post


Link to post
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

×