Report Request: Service Desk Ticket Duration
Hello,
I have 2 issues with the current report I'm running. I would like to add a coloum where it calculates the ticket duration from the moment the ticket is created to the closing of the ticket. Aslo, I have a custom field called "Users Affected." When I run the SQL report this field gets displayed as "Custom Field Value0" rather than "Users Affected." Is there any way to fix the display outcome of this? Here is the SQL of the report im running:
SELECT HD_TICKET.ID, HD_TICKET.TITLE, S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.CUSTOM_FIELD_VALUE0, O.FULL_NAME AS OWNER_NAME, HD_PRIORITY.NAME AS PRIORITY, HD_STATUS.NAME AS STATUS_NAME, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED FROM HD_TICKET LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND ((HD_TICKET.APPROVAL = '')) ORDER BY CREATED
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
aragorn.2003
9 years ago
First of all, please use the code style to print SQL statements.
Second, name the column with the AS command.
SELECT HD_TICKET.ID, HD_TICKET.TITLE, S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.CUSTOM_FIELD_VALUE0 AS AFFECTED_USERS, O.FULL_NAME AS OWNER_NAME, HD_PRIORITY.NAME AS PRIORITY,
HD_STATUS.NAME AS STATUS_NAME, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED
FROM HD_TICKET
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 3)
AND ((HD_TICKET.APPROVAL = ''))
ORDER BY CREATED
