Is it possible to create a report that will show all tickets resolved (or last modified) by a certain person? Currently have reports to show resolved tickets by technician but a ticket can be resolved (or last updated) by someone but technician name is left under another person.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

The following should get you started.  The limitation on date ranges and queue are arbitrary and only used to limit the result set.  This gives you the last user to touch a ticket.  So if a ticket is assigned to Jack but Jill adds the last comment, this will report Jill.

SELECT
    HD_TICKET.ID,
    HD_STATUS.NAME AS STATUS,
    HD_CATEGORY.NAME AS CATEGORY,
    S.FULL_NAME AS SUBMITTER_NAME,
    HD_TICKET.CREATED,
    HD_TICKET.TITLE,
    HD_TICKET_CHANGE.ID,
    HD_TICKET_CHANGE.COMMENT,
    HD_TICKET_CHANGE.USER_ID,
    LT.USER_NAME as 'Last touched by'
FROM
    HD_TICKET
        JOIN
    HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
        JOIN
    HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
        LEFT JOIN
    USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
        LEFT JOIN
    HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
        LEFT JOIN
    USER LT ON (LT.ID = HD_TICKET_CHANGE.USER_ID)
WHERE
    (HD_TICKET.HD_QUEUE_ID = 1)
        AND (((TIMESTAMP(HD_TICKET.CREATED) <= NOW()
        AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(), INTERVAL 7 DAY))))
        AND HD_TICKET_CHANGE.ID = (SELECT MAX(TC.ID)
FROM HD_TICKET_CHANGE TC
WHERE TC.HD_TICKET_ID = HD_TICKET.ID)
GROUP BY HD_TICKET.ID
ORDER BY HD_TICKET.ID
Answered 06/06/2014 by: grayematter
Fourth Degree Black Belt

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