Average time to complete triage

[Dan Munns]

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  

[Victor]

@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...!!!!  

[Dan Munns]

@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) 

[Victor]

Oh... widget... well ... what is that sorcery!!!  .. I have tried them purely in SQL...

Time to try them in the "thingie" you mentioned... widget was it?

[Victor]

hmmm.....

 

 

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  

[Dan Munns]

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....

[Dan Munns]

@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

[Victor]

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?

[Dan Munns]

@Victor if I use the single query I posted then no errors at all. Just outputs the 2.6 for the average over those calls in the filter. 

[Lyonel]

3 hours ago, Dan Munns said:

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

[Victor]

@Lyonel don't laugh.. it was written in very small letters... 

[Victor]

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