/bundles/itninjaweb/img/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:
Cancel
1 Comment   [ - ] Hide 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
Please log in to comment

Answer Chosen by the Author


Answers

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

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

Answers

Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share