Jump to content

Average time to complete triage


Dan Munns

Recommended Posts

Hi all, 

@Victor (as you are the go to guy for my SQL needs) 

I have been asked to create a widget to show the average time between a call being logged and the first task being completed. 

Is this something that can be done? If so.......how? 

Any help appreciated

Thanks  

Link to comment
Share on other sites

@Dan Munns

This gives you the request reference, the date it was logged, the date when the first task on eth request was completed and the difference between these in seconds:

SELECT h_pk_reference, h_datelogged, tasks.first_task_date, UNIX_TIMESTAMP(h_datelogged) - UNIX_TIMESTAMP(tasks.first_task_date) AS dif_seconds
FROM h_itsm_requests
LEFT JOIN (SELECT h_obj_ref_urn, MIN(h_completed_on) AS first_task_date FROM h_sys_tasks GROUP BY h_obj_ref_urn) AS tasks 
ON tasks.h_obj_ref_urn = h_itsm_requests.h_social_object_ref
WHERE tasks.first_task_date IS NOT NULL

This is to calculate the average (in seconds) of that difference:

SELECT AVG(UNIX_TIMESTAMP(h_datelogged) - UNIX_TIMESTAMP(tasks.first_task_date)) AS avg_dif_seconds
FROM h_itsm_requests
LEFT JOIN (SELECT h_obj_ref_urn, MIN(h_completed_on) AS first_task_date FROM h_sys_tasks GROUP BY h_obj_ref_urn) AS tasks
ON tasks.h_obj_ref_urn = h_itsm_requests.h_social_object_ref
WHERE tasks.first_task_date IS NOT NULL

But this gives you the average between the two for all requests that exist in your system... If you like that average per <something> (like per month or request status or date logged, closed, etc) you need to add these filters to the query...

Now, back to the most important point:

1 hour ago, Dan Munns said:

I have been asked to

This... this should not be ... should not be allowed...!!!! :D 

Link to comment
Share on other sites

@Victor Ok so maybe I am doing something wrong but when I paste those queries into the widget I get......blank.

My plan was to stick them in as is and then work out the filters (based on CI logged against) 

I am pasting them into the Custom Sql Query on a 'List of Data' widget.

Any ideas where I am going wrong? (I know you have) 

Link to comment
Share on other sites

hmmm.....

image.png

 

image.png

 

Maybe the blank is because,k as I said, is running through all the requests (and all the tasks) so the result might not come up quickly :) .. we need to make that query mo0re friendly for the resources by giving it some filters :) 

Link to comment
Share on other sites

We only have 11926 requests logged (ish) since go live. 

Also I get this error: MySQL Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT AVG(UNIX_TIMESTAMP(h_datelogged) - UNIX_TIMESTAMP(tasks.first_task_date))' at line 6

9 minutes ago, Victor said:

what is that sorcery!!!

That's what I want to know if you have it working....

Link to comment
Share on other sites

@Victor so does this work to show me the difference in days between date logged and date first task completed? Filter is call logged against a CI, which has been closed and logged this year.

SELECT ROUND(AVG(UNIX_TIMESTAMP(tasks.first_task_date) - UNIX_TIMESTAMP(h_datelogged))/86400, 1) AS "Difference in Days"
FROM h_itsm_requests
LEFT JOIN (SELECT h_obj_ref_urn, MIN(h_completed_on) AS first_task_date FROM h_sys_tasks GROUP BY h_obj_ref_urn) AS tasks 
ON tasks.h_obj_ref_urn = h_itsm_requests.h_social_object_ref
WHERE tasks.first_task_date IS NOT NULL AND h_catalog_id='95' AND (h_status='status.closed' OR h_status='status.resolved') AND (YEAR(h_datelogged) >= 2018)

The result I get is 2.6

Link to comment
Share on other sites

1 hour ago, Dan Munns said:

so does this work to show me the difference in days between date logged and date first task completed? Filter is call logged against a CI, which has been closed and logged this year

Yes, that should do it... do you still get the mySQL error or that's sorted?

Link to comment
Share on other sites

On 3/23/2018 at 3:03 PM, Dan Munns said:

if I use the single query I posted then no errors at all

... so the coin (my own coin) finally dropped :) ... so the queries I suggested are separate queries, to be used individually:

  • the first query gives you the request reference, the date it was logged, the date when the first task on eth request was completed and the difference between these in seconds
  • the second query gives you the average (in seconds) of the difference mentioned above

I just presented them both in case you want one info or another or just to explain the query returning the average a bit better... if you want the average number then only the second query will be used :) 

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
×
×
  • Create New...