/build/static/layout/Breadcrumb_cap_w.png

Looking to find a way to know number and list of persons that did any action on tickets!

For example a ticket (From Submit to Close) has had 5 persons who did action on that ticket to be completed! (whether they are owner of ticket or just do action on that).
I was wondering if you could help me to find a way know number and list of persons who/which did any action on tickets.

It's for evaluation of departments to find load of works of each organization based on action which is done on tickets!

Thank you :)



1 Comment   [ + ] Show comment
  • Try this:-

    SELECT USER.FULL_NAME AS CHANGED_BY,
    USER.ID,
    T.HD_TICKET_ID AS TICKET_NO,
    T.DESCRIPTION
    FROM HD_TICKET_CHANGE T
    JOIN USER ON (USER.ID = T.USER_ID)
    WHERE T.DESCRIPTION LIKE '%Changed ticket Status from "New" to "Closed".%'
    GROUP BY TICKET_NO - Druis 2 years ago
    • Thank you Duris for your help. :)
      I think that my question is not clear enough, so let me ask it again with more details! Sorry for your inconvenience.
      What I need as result of report is this:
      Closed Ticket IDs for last 1 month / How many person did action on each ticket...for example 5 / Name of this 5 person which did action on this ticket!

      Thank you again - Mett 2 years ago

Answers (1)

Answer Summary:
Posted by: JasonEgg 2 years ago
Red Belt
1

Top Answer

SELECT T.ID AS TICKET_ID, 
  T.TIME_CLOSED,
  COUNT(DISTINCT USER.ID) AS 'Number of users',
  GROUP_CONCAT(DISTINCT USER.USER_NAME) AS 'User names'
FROM HD_TICKET T
  JOIN HD_TICKET_CHANGE C on C.HD_TICKET_ID = T.ID
  JOIN USER ON USER.ID = C.USER_ID
WHERE T.TIME_CLOSED > SUBDATE(NOW(),INTERVAL 1 MONTH)
GROUP BY T.ID
 
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