I am using the following code but I need some help making revisions to it.

CONCAT('TICK:',HD_TICKET.ID) as 'Number' ,HD_PRIORITY.NAME as 'Priority' ,Q.NAME as 'Queue' ,HD_TICKET.TITLE as 'Title' ,if((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as 'Owner' ,DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as 'Created' ,case upper(STATE)
                               when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) -
                                                    unix_timestamp(HD_TICKET.TIME_OPENED)
                               when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
                               else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end
                                as 'Time Open' ,HD_CATEGORY.NAME as 'Category' ,HD_STATUS.NAME as 'Status' ,if((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as 'Submitter' ,HD_TICKET.DUE_DATE as 'Due'  from (HD_TICKET, HD_STATUS, HD_IMPACT, HD_CATEGORY)
                        LEFT JOIN HD_PRIORITY on (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
                        LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
                        LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
                        LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
                        LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
                        where (HD_TICKET.HD_STATUS_ID = HD_STATUS.ID  and
                        HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID and HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) and ((( (HD_TICKET.HD_QUEUE_ID in (5)))  or (OWNER_ID = 314) or (APPROVER_ID = 314) or (SUBMITTER_ID = 314 and (SERVICE_TICKET_ID is NULL or SERVICE_TICKET_ID = 0 or IS_PARENT = true)))
                   and STATE in ('opened', 'stalled'))  

1) How can I get rid of the following columns:

  • Time Open
  • Category
  • Status 
2) Relocate the "Due Date" column to the right of the "Created" column.

3) This report is including tickets from another ticket queue.  Can you please help me set it to queue 5 which is the "Marketing" queue?

Thanks in advance!
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
To get rid of these 3 columns, just remove
case upper(STATE) when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED) when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)             else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as 'Time Open'
HD_CATEGORY.NAME as 'Category'
HD_STATUS.NAME as 'Status'

To relocate the Due Date columt right of the created column, move

HD_TICKET.DUE_DATE as 'Due'

right after

DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as 'Created',

To ensure that you only see tickets from queue 5, it´s correct to have the where clause

HD_TICKET.HD_QUEUE_ID in (5)

but you have several or conditions. please change that one.

Answered 03/09/2015 by: aragorn.2003
Red Belt

  • Hi Aragorn,

    The report shows up fine but it still contains tickets from other queues. For example, the Network report includes 2 tickets for the Help Desk (Queue:1) and 3 tickets for Accounting (Queue:4).
    • Hi TXgroup. As I said, you have so many or conditions. What you get is a list with tickets in queue_id = 5 or where the owner is 314 (also in other queues) or where the approver is 314 (also in other queues) or where the submitter is 314 (also in other queues). I think you need to walk through the whole where condition.
      • Hi Aragorn, I was able modify the owner, approver, and submitter to the same queue number and that resolved the issue! Thanks!!
      • This content is currently hidden from public view.
        Reason: Removed by member request
        For more information, visit our FAQ's.
Please log in to comment
Answer this question or Comment on this question for clarity