/build/static/layout/Breadcrumb_cap_w.png

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)

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