I've produced some reports using the Request Metrics set of data under Custom Reports. These are great at giving things like average response time and average resolution time, but I wanted to dig a bit deeper into the data so I could really trust what it was telling me.
I found a detailed First Response Time report at the link below:
This works, but it does not give me a full data set because many of our resolved requests have no first response time. The technicians will resolve them with basic info on the Resolution field, without replying first. This is OK for simple cases, but such tickets are not counted in the First Response Time summary and they really need to be, because we did respond - at the point of resolution.
To correct this, I want to set the 'first response time' to be equal to the 'resolution time', any time the first response is blank/zero/null.
Try as I might, I cannot get the query to substitute the first response time with the resolution time. Could someone point me in the right direction? My SQL knowledge is OK, but hardly spectacular.
I am editing this line of the query from the above link (PGSQL version):
LONGTODATE(wo.RESPONDEDTIME) "Responded Date",
None of the following work. The query fails to run, with no error displayed.
CASE WHEN wo.RESPONDEDTIME = 0 THEN LONGTODATE(wo.RESOLVEDTIME) ELSE LONGTODATE(wo.RESPONDEDTIME) END "Responded Date",
I've tried "is Null", "= Null", "= 0" and other options, just in case. I've also tried putting LONGTODATE( ) around the "CASE... END" statement. No luck.
This also doesn't work, which could have been an option if the response time was Null:
COALESCE (LONGTODATE(wo.RESPONDEDTIME), LONGTODATE(wo.RESOLVEDTIME)) "Responded Date",
Again, with LONGTODATE( ) around the COALESCE statement makes no difference.
If I remove the LONGTODATE conversion some of the above will work, but of course the returned result is not so useful to view.
I would appreciate any sage advice from someone with more knowledge, thanks!