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:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • Good afternoon all.  I would like to also see the technician / owner the ticket is assigned to.  How would I go about doing that?

Please log in to comment

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

Answered 12/07/2012 by: jverbosk
Red Belt

  • Thank you this is perfect!

  • No problem, thanks for the feedback!

    John

  • Any way to filter out tickets based on priority, say WHERE (HD_PRIORITY.NAME != '5')  ???

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

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
Fourth Degree Black Belt

  • 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
Answer this question or Comment on this question for clarity