/build/static/layout/Breadcrumb_cap_w.png
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,
Answer Summary:
2 Comments   [ + ] Show comments

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
    • 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.
  • 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!

Answer Chosen by the Author

1
Use timestampdiff:

timestampdiff(HOUR, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED) AS "Time to Close"


Answered 08/02/2018 by: chucksteel
Red Belt

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