Hardware category report for the past 30 days
We are trying to create a report for all tickets opened under the Hardware category over the past 30 days in a specific queue.
We have run the following SQL query, but this is generating all tickets from all categories in the specific queue:
SELECT T.ID AS 'Ticket #', T.TITLE AS 'Issue', S.NAME AS 'Status' FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
WHERE DATEDIFF(NOW(), CREATED) < 30 AND T.HD_QUEUE_ID = 1
If anyone could provide any additional info on getting the Hardware category isolated in the report that would be great.
Thanks in advance!
Answer Chosen by the Author
This worked in my system when I changed hardware to internal. Check the case sensitivity of the word hardware. Hardware or hardware.
HD_TICKET.CREATED,HD_CATEGORY.NAME AS CATEGORY,
HD_TICKET.TITLE,S.FULL_NAME AS SUBMITTER_NAME,
O.FULL_NAME AS OWNER_NAME
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND (HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 31 DAY) ) AND (HD_CATEGORY.NAME like '%hardware%')
ORDER BY CREATED desc,CATEGORY
Answered 04/13/2016 by: