/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: chucksteel 9 years ago
Red Belt
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.

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

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ