/build/static/layout/Breadcrumb_cap_w.png
11/21/2018 138 views

I'm trying to create a custom report that will tell me the number or times a User made any change to any ticket within a time frame. Essentially get work hours for an entire Queue based on ticket touches... However, I'm trying to do this by blending other posts, which has led to a great deal of failure on my part. 


Anyone done something similar they might share? 

Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • SELECT HD_QUEUE.NAME as Queue, HD_CATEGORY.NAME AS Category,
    (SELECT COUNT(ID) FROM HD_TICKET where
    HD_CATEGORY_ID = HD_CATEGORY.ID
    and HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 1 MONTH)GROUP BY HD_CATEGORY.ID) as Tickets,
    USER.FULL_NAME as "Default Owner"
    FROM HD_CATEGORY
    LEFT JOIN HD_QUEUE on HD_QUEUE.ID = HD_CATEGORY.HD_QUEUE_ID
    LEFT JOIN USER on USER.ID = HD_CATEGORY.DEFAULT_OWNER_ID

    GROUP BY HD_CATEGORY.ID
    ORDER BY HD_QUEUE.NAME, HD_CATEGORY.NAME


    This is somewhat what I want, and mostly what I was basing my guesses off of, but I don't actually care for Category info. All attempts at editing this to show Count if tickets edited by a user in a particular Queue fail to even Save.

Answer Chosen by the Author

1

This should work for you:

SELECT USER.FULL_NAME, COUNT(DISTINCT(HD_TICKET_CHANGE.HD_TICKET_ID)) as '# Changed',
GROUP_CONCAT(DISTINCT(HD_TICKET_CHANGE.HD_TICKET_ID)) as'Tickets'
FROM USER
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.USER_ID = USER.ID
WHERE HD_TICKET_CHANGE.TIMESTAMP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
and USER.USER_NAME = "username"
GROUP BY USER.FULL_NAME

This uses username for the query instead of user full name, just set it to the appropriate username. It also gives a comma separated list of the tickets that the user changed.

Answered 11/26/2018 by: chucksteel
Red Belt

  • This should do quite nicely. I greatly appreciate it, Sir.
  • I have added

    || HD_TICKET_CHANGE.TIMESTAMP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
    and USER.USER_NAME = "2nd username"

    Following the initial user to expand to multiple people. Seems to be working well. Thanks again.

All Answers

0

If you are reporting on multiple users (which is what your comment suggests), then this query reports on users in a given label (User Services, in this example):

SELECT USER.FULL_NAME, COUNT(DISTINCT(HD_TICKET_CHANGE.HD_TICKET_ID)) as "Tickets Changed",
GROUP_CONCAT(DISTINCT(HD_TICKET_CHANGE.HD_TICKET_ID))
FROM USER
JOIN USER_LABEL_JT on USER_LABEL_JT.USER_ID = USER.ID
JOIN LABEL on LABEL.ID = USER_LABEL_JT.LABEL_ID
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.USER_ID = USER.ID
WHERE LABEL.NAME = "User Services"
and HD_TICKET_CHANGE.TIMESTAMP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY USER.FULL_NAME
ORDER BY USER.FULL_NAME


Answered 11/26/2018 by: chucksteel
Red Belt

  • That is much more efficient. Thanks again hahah