/build/static/layout/Breadcrumb_cap_w.png

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

 


1 Comment   [ + ] Show comment
  • Good afternoon all. I would like to also see the technician / owner the ticket is assigned to. How would I go about doing that? - kwadley 9 years ago
    • Is adding the technical owner of the ticket something that can be added to this report?

      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

Answers (2)

Answer Summary:
Posted by: jverbosk 11 years ago
Red Belt
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
  • No problem, thanks for the feedback!

    John - jverbosk 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
Posted by: WhitzEnd 11 years ago
7th Degree Black Belt
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


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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ