Miscellaneous : Want to change time on Tickets Closed report to just display total hours and not days, minutes or seconds

Want to change time on Tickets Closed report to just display total hours and not days, minutes or seconds

I tend to do alot of Pivot Table work and produce charts. For my small org, time to close should really just be reported in hours and not broken into Days, minutes, seconds. I now modify this once I have the report in Excel, because I know how to do so. I don't know how to do this with SQL. I believe this is the relevant part of the report query. Can anyone give me the proper syntax do get this to report in just hours?

TIME_TO_SEC(HD_TICKET.TIME_OPENED),

TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED),

TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',

DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",

SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED)-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),

'%kh %im')) AS TIME_TO_CLOSE,

2 Comments [ + ] Show comments

Thanks, Chuck. I tried to find several places to put this, but I keep getting SQL errors.
select HD_TICKET.ID,
HD_TICKET.TITLE ,
DATE_FORMAT(HD_TICKET.TIME_OPENED, '%m-%d-%y') as DATE_OPENED,
DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d-%y') as DATE_CLOSED,
HD_TICKET.CUSTOM_FIELD_VALUE2 as DEPARTMENT,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join timestampdiff(HOUR, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED) AS Time to Close
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
JOIN USER_FIELD_VALUE DEPARTMENT on DEPARTMENT.USER_ID = HD_TICKET.SUBMITTER_ID and DEPARTMENT.FIELD_ID = 2
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 90 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL - ForneyGonzo 5 years ago
- Well, it isn't a join statement, it should be in the select clause with the other columns being selected.
  
  Put a comma after SUBMITTER_NAME and place the timestampdiff(HOUR, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED) AS Time to Close on the next line.
  
  Also, if you want the column name to be "Time to Close" then it needs to be in quotes because of the spaces, like it was in my example. - chucksteel 5 years ago
I had originally put it in the select area, but obviously didn't use the comma and I believe I also interrupted another select statement in my haste. Thanks! - ForneyGonzo 5 years ago