I have seen many reports on IT Ninja, but I am looking for one that I hope someone can help me with. 

 

I am looking to finish this report and report on tickets that are 2 days or older, and need help finishing this.  Thanks everyone

 

SELECT HD_TICKET.ID, Q.NAME AS QUEUE_NAME, HD_CATEGORY.NAME AS CATEGORY, GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED, S.FULL_NAME AS SUBMITTER_NAME, O.USER_NAME AS OWNER_USER_NAME, HD_PRIORITY.NAME AS PRIORITY, HD_STATUS.NAME AS STATUS_NAME, HD_TICKET.CREATED, HD_TICKET.TIME_OPENED  FROM HD_TICKET  JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) 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 = 41) AND ((HD_STATUS.NAME = 'New') AND (HD_STATUS.NAME = 'Open') AND ((Q.NAME = '1 - North America') OR (Q.NAME = 'IT Network/Telecom') OR (Q.NAME = 'IT Infrastructure Systems') OR (Q.NAME = 'IT End User Computing')))  GROUP BY HD_TICKET.ID ORDER BY ID.

 

 

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
There were some issues with your selection criteria.  Primarily the (HD_STATUS.NAME = 'New') AND (HD_STATUS.NAME = 'Open') bit.  A ticket can have a status of 'New' OR 'Open', not both.  So this will prevent any results from showing.

As for the 2 days bit, give the following a try.
SELECT 
    HD_TICKET.ID,
    Q.NAME AS QUEUE_NAME,
    HD_CATEGORY.NAME AS CATEGORY,
    GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT
        SEPARATOR '
                ') AS HD_TICKET_CHANGE_COMMENT_GROUPED,
    S.FULL_NAME AS SUBMITTER_NAME,
    O.USER_NAME AS OWNER_USER_NAME,
    HD_PRIORITY.NAME AS PRIORITY,
    HD_STATUS.NAME AS STATUS_NAME,
    HD_TICKET.CREATED,
    HD_TICKET.TIME_OPENED
FROM
    HD_TICKET
        JOIN
    HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID
        JOIN
    HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
        LEFT JOIN
    HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
        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_STATUS.NAME = 'New')
        OR (HD_STATUS.NAME = 'Open'))
        AND ((Q.ID = 41)
        OR (Q.NAME = '1 - North America')
        OR (Q.NAME = 'IT Network/Telecom')
        OR (Q.NAME = 'IT Infrastructure Systems')
        OR (Q.NAME = 'IT End User Computing'))
        and HD_TICKET.CREATED < date_sub(now(), interval 2 day)
GROUP BY HD_TICKET.ID
ORDER BY ID
Answered 08/07/2014 by: grayematter
Fourth Degree Black Belt

  • Grayematter,

    Thank you for your prompt response. I feel that I am very close. Is there a way to take the Time opened column, and display in overall elapsed time from creation.

    Forexample:

    Ticket Datetime Days_old
    34581 2014-07-27 10.3

    Where Days Old is displayed in time
    • Try adding one of these to the SELECT part of the query. You may want CREATED instead of TIME_OPENED, depending on the exact measure you seek.

      datediff(NOW(), HD_TICKET.TIME_OPENED) as elapsed1,

      date_format(sec_to_time( timestampdiff(second, HD_TICKET.TIME_OPENED, NOW())), '%m-%d %H:%i') as elapsed2,
      • Thank you for your help, as this has helped my tremendously.
Please log in to comment
Answer this question or Comment on this question for clarity