Report showing number of tickets that a user has that are over 3 days old taking into account a 5 day work week.
My manager found this query on these forums and hopefully the author or someone else knowledgeable can assist. This gets the tickets that are over 3 days old and displays them. Now they would like to have a report that shows users, and how many tickets they have that are over 3 days old taking into account the weekends etc.
Is there something easy I can add to this query or do I need to bark up a different tree?
SELECT T.ID AS 'TICK', T.TITLE AS 'Problem Description', U.FULL_NAME AS 'Owner', T.CREATED AS 'Time Created', CONCAT(DATEDIFF(NOW(), T.CREATED),'D') AS 'Time Opened', P.NAME AS 'Priority' FROM HD_TICKET T
JOIN USER U ON (U.ID = T.OWNER_ID)
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
JOIN HD_PRIORITY P ON (P.ID = T.HD_PRIORITY_ID)
JOIN HD_QUEUE Q ON (T.HD_QUEUE_ID = Q.ID AND Q.NAME ='IT')
WHERE NOW() > DATE_ADD(T.CREATED, INTERVAL 5 DAY) AND T.OWNER_ID <> 0 AND S.STATE = 'opened'
ORDER BY T.CREATED
Answers (2)
Based on your reply to WhitzEnd, I think this should work for you. This will list the ticket submitters (USER1, etc), the total number of open tickets greater than 3 days, and the ticket numbers themselves.
Change the INTERVAL # DAY to whatever you need - you said 3 days initially so that's what this report does, but if you need it to be 30, just change it from 3 to 30 (or whatever).
When you setup the report, just give it a title and/or description of "Users with Tickets Open Over 3 Days" and it will appear in the report when generated.
FYI, if you need an actual "days open" count per ticket that can be done, but from your comment it appears you just need a total count of tickets.
Hope that helps!
John
________________________
SELECT U.FULL_NAME AS SUBMITTER, COUNT(T.ID) AS TOTAL,
GROUP_CONCAT(CAST(T.ID AS CHAR) ORDER BY 1 SEPARATOR ', ') AS TICKETS
FROM HD_TICKET T
JOIN USER U ON (U.ID = T.SUBMITTER_ID)
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
WHERE NOW() > DATE_ADD(T.CREATED,INTERVAL 3 DAY)
AND DAYOFWEEK(T.CREATED) NOT IN (1,7)
AND S.STATE = 'opened'
GROUP BY U.FULL_NAME
ORDER BY U.FULL_NAME
Comments:
-
Thank you this is perfect! - dhatt 11 years ago
-
-
Any way to filter out tickets based on priority, say WHERE (HD_PRIORITY.NAME != '5') ??? - dhatt 11 years ago
-
Sure, give this a shot:
SELECT U.FULL_NAME AS SUBMITTER, COUNT(T.ID) AS TOTAL,
GROUP_CONCAT(CAST(T.ID AS CHAR) ORDER BY 1 SEPARATOR ', ') AS TICKETS,
GROUP_CONCAT(P.NAME ORDER BY 1 SEPARATOR ', ') AS PRIORITY
FROM HD_TICKET T
JOIN USER U ON (U.ID = T.SUBMITTER_ID)
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
JOIN HD_PRIORITY P ON (P.ID = T.HD_PRIORITY_ID)
WHERE NOW() > DATE_ADD(T.CREATED,INTERVAL 3 DAY)
AND DAYOFWEEK(T.CREATED) NOT IN (1,7)
AND S.STATE = 'opened'
AND P.NAME not rlike 'none'
GROUP BY U.FULL_NAME
ORDER BY U.FULL_NAME
You can yank the third line (and the comma after "AS TICKETS') if you don't want a column for PRIORITY. Also, the PRIORITY.NAME field uses words, not numbers, so you can adjust the third-to-the-last line like this:
AND P.NAME not rlike 'none|low|medium'
AND P.NAME rlike 'medium|high'
I.e., exclude priority types using "not rlike" or include them using "rlike". I prefer rlike (REGEX) to like since it's easy to tweak and takes a single instance fine. I'd also recommend using PRIORITY.NAME instead of PRIORITY.ID since this will work across multiple queues.
Please let me know if you have any questions, otherwise hope that helps!
John - jverbosk 11 years ago
Give this a try.
SELECT T.ID AS 'TICK', T.TITLE AS 'Problem Description', U.FULL_NAME AS 'Owner', T.CREATED AS 'Time Created', CONCAT((dayofweek(now()) in (1,2,3) and created <= (now() - interval 5 day)) or
(dayofweek(now()) >= 4 and created <= (now() - interval 3 day))) AS 'Time Opened', P.NAME AS 'Priority' FROM HD_TICKET T
JOIN USER U ON (U.ID = T.OWNER_ID)
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
JOIN HD_PRIORITY P ON (P.ID = T.HD_PRIORITY_ID)
JOIN HD_QUEUE Q ON (T.HD_QUEUE_ID = Q.ID AND Q.NAME ='IT')
WHERE NOW() > DATE_ADD(T.CREATED, INTERVAL 5 DAY) AND T.OWNER_ID <> 0 AND S.STATE = 'opened'
ORDER BY T.CREATED
Comments:
-
Thank you for your answer but I'm afraid this does much the same as the code I posted. I need the out put to be:
USER1 has X tickets over 30 days old
USER2 has Y tickets over 30 days old
etc. - dhatt 11 years ago
SELECT U.FULL_NAME AS SUBMITTER, COUNT(T.ID) AS TOTAL,
GROUP_CONCAT(CAST(T.ID AS CHAR) ORDER BY 1 SEPARATOR ', ') AS TICKETS
FROM HD_TICKET T
JOIN USER U ON (U.ID = T.SUBMITTER_ID)
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
WHERE NOW() > DATE_ADD(T.CREATED,INTERVAL 3 DAY)
AND DAYOFWEEK(T.CREATED) NOT IN (1,7)
AND S.STATE = 'opened'
GROUP BY U.FULL_NAME
ORDER BY U.FULL_NAME - kwadley 9 years ago