Filtering to just closed tickets in a report
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
Answers (1)
Please log in to answer
Posted by:
chucksteel
10 years ago
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.
