/build/static/layout/Breadcrumb_cap_w.png
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 :)


Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • 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
    • 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

Answer Chosen by the Author

1
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
Answered 05/22/2018 by: JasonEgg
Red Belt