Tweaking of a report
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