I have been working on a goal to have Kace Service Desk ticket reports to display total ticket times from both NEW to OPEN and NEW to CLOSED and I have run accross a problem.  When I use the TIME_TO_SEC function within a monthly report, out of 100 entries, 2 entries show the TIME_TO_SEC of the ticket, CREATED seconds is smaller number than the CLOSED.  I then noticed our times are off when adding up the total times for all the tickets in a month and comparing it to the report.  It gets really weird when I do a cum over several months, one entry which lies outside the criteria thay may show 34 days+.  I am trying to discover whether it is corrupt data or incorrect use of coding.  Here is the code I am working with.

SELECT
(case when
HD_TICKET.CREATED=HD_TICKET.TIME_OPENED then 'All Tickets'
when
HD_TICKET.CREATED<DATE_ADD(IF(HD_TICKET.TIME_OPENED='0',IF(HD_TICKET.TIME_CLOSED='0',0,HD_TICKET.TIME_CLOSED),HD_TICKET.TIME_OPENED),
INTERVAL 1 HOUR) then 'All Tickets'
when
HD_TICKET.CREATED<DATE_ADD(IF(HD_TICKET.TIME_OPENED='0',IF(HD_TICKET.TIME_CLOSED='0',0,HD_TICKET.TIME_CLOSED),HD_TICKET.TIME_OPENED), INTERVAL
24 HOUR) then 'All Tickets'
when
HD_TICKET.CREATED>DATE_ADD(IF(HD_TICKET.TIME_OPENED='0',IF(HD_TICKET.TIME_CLOSED='0',0,HD_TICKET.TIME_CLOSED),HD_TICKET.TIME_OPENED),INTERVAL
24 HOUR) then 'All Tickets'
end ) as CLOSE_GROUP,

count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
CONCAT((sum(TIME_TO_SEC(TIMEDIFF(IF(HD_TICKET.TIME_OPENED='0',IF(HD_TICKET.TIME_CLOSED='0',0,HD_TICKET.TIME_CLOSED),HD_TICKET.TIME_OPENED),HD_TICKET.CREATED)))/count(HD_TICKET.ID)
div 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(IF(HD_TICKET.TIME_OPENED='0',IF(HD_TICKET.TIME_CLOSED='0',0,HD_TICKET.TIME_CLOSED),HD_TICKET.TIME_OPENED),HD_TICKET.CREATED)))/count(HD_TICKET.ID) mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_OPEN from HD_TICKET,HD_STATUS
where
HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
and CREATED>='2014-10-01' /*change the start date here*/
and CREATED<='2014-10-31' /*change the end date here*/
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
group by CLOSE_GROUP

0 Comments   [ + ] Show Comments

Comments

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment

Answers

0
This is actually a SEC_TO_TIME issue. It will only convert up to 838:59:59
Answered 11/04/2014 by: h2opolo25
Red Belt

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
  • h2opolo25, This is just one instance that I have noticed. It also happens in this code which does not do SEC_TO_TIME.

    SELECT HD_TICKET.ID, O.FULL_NAME AS OWNER_NAME, HD_TICKET.CREATED, HD_TICKET.TIME_OPENED, HD_TICKET.TIME_CLOSED, TIME_TO_SEC(TIMEDIFF( IF(HD_TICKET.TIME_OPENED='0', IF(HD_TICKET.TIME_CLOSED='0', 0 , HD_TICKET.TIME_CLOSED), HD_TICKET.TIME_OPENED), HD_TICKET.CREATED))/60 as Time_Diff_Min FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) where (HD_TICKET.HD_QUEUE_ID = 1) AND HD_TICKET.CREATED>='2014-10-01' and HD_TICKET.CREATED<='2014-10-31' so that leads me to believe it is the TIME_TO_SEC that is giving me the issue.

    Thanks
    • Looking at this second code you posted... I think the problem is that you put single quotes around the 0's for TIME_OPENED and TIME_CLOSED. If you really want to show it with single quotes you need to put '0000-00-00 00:00:00' or you can just write 0 without quotes.
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share