/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 11 years ago
Red Belt
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.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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