I have this report that shows me the duration that a ticket was open. However, it's showing all tickets so the tickets that are currently open or stalled are showing negative numbers (00:00:00 as the close date). I can't seem to figure out how to filter to just the closed tickets. Can anybody help?

SELECT HD_CATEGORY.NAME AS CATEGORY, HD_TICKET.CREATED AS HD_TICKET_CREATED, HD_IMPACT.NAME AS IMPACT, O.FULL_NAME AS OWNER_NAME, HD_PRIORITY.NAME AS PRIORITY, S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.TIME_CLOSED AS HD_TICKET_TIME_CLOSED, HD_TICKET.TIME_OPENED AS HD_TICKET_TIME_OPENED, HD_TICKET.TITLE AS HD_TICKET_TITLE, (time_to_sec(timediff(HD_TICKET.TIME_CLOSED,HD_TICKET.CREATED))/3600) as DURATION FROM HD_TICKET 

JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) JOIN HD_IMPACT ON (HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) WHERE ( HD_TICKET.HD_QUEUE_ID =  3 or 5 or 15 or 16 or 17 or 18 or 19 or 20 or 21 or 22 or 23 or 24 or 25 or 26 or 27) AND ( (DATE(HD_TICKET.CREATED)> DATE_SUB(NOW(), INTERVAL 1 month) AND DATE(HD_TICKET.CREATED)<= NOW() ) ) ORDER BY O.FULL_NAME asc, HD_PRIORITY.ORDINAL asc, HD_TICKET.CREATED asc, HD_TICKET.TIME_CLOSED asc, HD_TICKET.TITLE asc, S.FULL_NAME asc, HD_IMPACT.NAME asc, HD_CATEGORY.NAME asc, HD_TICKET.CUSTOM_FIELD_VALUE0 asc, HD_TICKET.CUSTOM_FIELD_VALUE1
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
You can add this to your where clause:
and HD_TICKET.TIME_CLOSED != '0000-00-00 00:00:00'

Tickets that are still open have that value, when the ticket is closed the value will be set.

Answered 11/17/2014 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share