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

Please log in to comment

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

Please log in to comment
Answer this question or Comment on this question for clarity