Tweaking of a report
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
Answers (1)
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.
