Question: Report showing number of tickets that a user has that are over 3 days old taking into account a 5 day work week.

Report showing number of tickets that a user has that are over 3 days old taking into account a 5 day work week.

dhatt

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

Answer Summary:

Answered

Please log in to comment

Answers

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

Answered 12/07/2012 by:

jverbosk

dhatt 5 months 8 days 13 mins ago

Thank you this is perfect!
jverbosk 5 months 8 days 31 mins ago

No problem, thanks for the feedback!

John
dhatt 4 months 4 days 34 mins ago

Any way to filter out tickets based on priority, say WHERE (HD_PRIORITY.NAME != '5') ???
jverbosk 4 months 4 days 22 mins 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

Please log in to comment

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

Answered 12/05/2012 by:

WhitzEnd

dhatt 5 months 12 days 1 mins ago

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.

Please log in to comment

This content is currently hidden from public view.

Reason: Removed by member request
For more information, visit our FAQ's.

Please log in to answer