/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Tweaking of a report

05/17/2012 2905 views

Hi,

Using the below SQL code i have managed to create a report that i can export into Excel, but i need some help/advice in changing the output to just show minutes instead of hrs, mins, secs. The point is to allow an avergae to be calculated in Excel for ticket closure times per month. Hope someone can help.

 

SELECT T.ID as 'Ticket Number', C.NAME AS 'Category', T.TITLE AS 'Title', concat( truncate( ( timestampdiff(HOUR,T.CREATED, T.TIME_CLOSED) ) /24, 0 ) , " Days ", ( timestampdiff(HOUR,T.CREATED,T.TIME_CLOSED) ) %24, " Hrs ", ( timestampdiff(MINUTE,T.CREATED,T.TIME_CLOSED) )  %60, " Mins ", ( timestampdiff(SECOND,T.CREATED, T.TIME_CLOSED) ) %60, " Secs" ) AS 'Time to Close' FROM HD_TICKET T JOIN HD_CATEGORY C ON C.ID=T.HD_CATEGORY_ID JOIN HD_STATUS S ON T.HD_STATUS_ID=S.ID and S.STATE='Closed' WHERE (DATE(CURDATE() - INTERVAL 30 DAY) < DATE(T.TIME_CLOSED)) ORDER BY CATEGORY asc, TIME_CLOSED asc

0 Comments   [ + ] Show comments

Comments


All Answers

2

I believe you need to replace:

concat( truncate( ( timestampdiff(HOUR,T.CREATED, T.TIME_CLOSED) ) /24, 0 ) , " Days ", ( timestampdiff(HOUR,T.CREATED,T.TIME_CLOSED) ) %24, " Hrs ", ( timestampdiff(MINUTE,T.CREATED,T.TIME_CLOSED) )  %60, " Mins ", ( timestampdiff(SECOND,T.CREATED, T.TIME_CLOSED) ) %60, " Secs" ) AS 'Time to Close' 

With:

timestampdiff(MINUTE, T.CREATED, T.TIME_CLOSED) AS 'Time to Close'

I didn't test that, but try it out and let me know if it works.

Answered 05/17/2012 by: chucksteel
Red Belt

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ