HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

Measuring ticket handling -- sql query question


#1

Ian,

I am trying to find a proxy measure for how much effort we spend on tickets. I’m thinking that number of times a ticket is touched (meaning updated) is a decent proxy.

I created the following query. I think it gives me the number of times a staff member updates any ticket (in the time of interest), and the number of total unique tickets that the staff member handles (during the time frame of interest).

Am I correct? I’m new to this SQL stuff and not sure my assumptions about your data structure are right. Thanks in advance.

Pete

“SELECT CONCAT(HS_Person.sFname,’ ‘, HS_Person.sLname) AS ‘Staff Member’, COUNT(DISTINCT HS_Request_History.xRequest) AS ‘Tickets’, COUNT(HS_Request_History.xRequest) AS ‘Touches’
FROM HS_Request_History, HS_Person, HS_Request
WHERE HS_Request_History.dtGMTChange<=UNIX_TIMESTAMP(’{_GET['enddate']}') AND HS_Request_History.dtGMTChange>UNIX_TIMESTAMP('{_GET[‘startdate’]}’)
AND HS_Request_History.xPerson=HS_Person.xPerson
AND HS_Request_History.xRequest=HS_Request.xRequest
GROUP BY HS_Person.xPerson
ORDER BY ‘Touches’”


#2

Yep that looks like it works. I did some quick tests on a local installation and the numbers seem to match up. Nice job!

Also in the next release there’s a time tracking module so you could track actual time, but I find in organizations that aren’t actually billing on the time that it often doesn’t get done :frowning: