Dan Munns Posted March 23, 2018 Share Posted March 23, 2018 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 More sharing options...
Victor Posted March 23, 2018 Share Posted March 23, 2018 @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...!!!! Link to comment Share on other sites More sharing options...
Dan Munns Posted March 23, 2018 Author Share Posted March 23, 2018 @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 More sharing options...
Victor Posted March 23, 2018 Share Posted March 23, 2018 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? Link to comment Share on other sites More sharing options...
Victor Posted March 23, 2018 Share Posted March 23, 2018 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 Link to comment Share on other sites More sharing options...
Dan Munns Posted March 23, 2018 Author Share Posted March 23, 2018 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 More sharing options...
Dan Munns Posted March 23, 2018 Author Share Posted March 23, 2018 @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 More sharing options...
Victor Posted March 23, 2018 Share Posted March 23, 2018 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 More sharing options...
Dan Munns Posted March 23, 2018 Author Share Posted March 23, 2018 @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. Link to comment Share on other sites More sharing options...
Lyonel Posted March 23, 2018 Share Posted March 23, 2018 3 hours ago, Dan Munns said: @Victor (as you are the go to guy for my SQL needs) Link to comment Share on other sites More sharing options...
Victor Posted March 23, 2018 Share Posted March 23, 2018 @Lyonel don't laugh.. it was written in very small letters... 1 Link to comment Share on other sites More sharing options...
Victor Posted March 28, 2018 Share Posted March 28, 2018 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now