HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

SQL Script


#1

Hi Ian,

I posted a topic eatlier:

I’m trying to run a single report in 2.7.0 showing the total number of calls over a fixed period (eg 30 days) and what percentage of those calls are closed within different time parameters i.e. how many were closed within 30mins then how many within 4hrs and then how many within 24hrs and then how many with 48 hrs etc…
Is there one report that can do this? any help will be much appreciated.

There’s not currently a report that breaks it out like that. The resolution speed report will give you averages and medians but not a count of X in Y hours, etc. That could be done via direct SQL especially if you’re not worried about business hours and just total time from open to closed.
In that case you just minus the closing time from the starting time in HS_Request and do your counts as needed.

I’m not familiar with SQL - but I know someone who is, if possible could you please send me a script for this, with the above parameters and then I can pass it on to them.

Thanks.


#2

Hi Rob,

The basic SQL for this would be to get the number of seconds between open and closing time and then limit that by date range. Here’s an example:

SELECT (dtGMTClosed - dtGMTOpened) AS close_speed
FROM HS_Request
WHERE dtGMTOpened > #START-RANGE# AND dtGMTOpened <= #END-RANGE#

Where start/end range are unix timestamps. You’ll then have the number of seconds and can group them, etc. You could also get more details from HS_Request like the request ID, customer name, etc.