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

Comments

Please log in to comment

Answers

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 
Answered 04/07/2015 by: aragorn.2003
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity