The needed report is for the service desk.  The report should have the following: shows what was open in the last week - what tickets each person still has open (ID, title, Timeopen, submitter, status, priority), and what tickets they have closed during the past 7 days.

what I have so far for sql is this, but it does not function in the manner we desire. Tickets with different status are mixed in the report as well as separate queues, ie maintenance and it queues.  We need a separate report for each queue and the tickets organized by status.

SELECT T.ID AS 'Ticket #', T.TITLE AS 'Issue', T.TIME_OPENED AS 'Time Opened', IFNULL(O.FULL_NAME,'Unassigned') AS 'Owner', U.FULL_NAME AS 'Submitter', S.NAME AS 'Status', P.NAME AS 'Priority' FROM HD_TICKET T
 JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
 JOIN USER U ON (T.SUBMITTER_ID = U.ID)
 LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
 JOIN HD_PRIORITY P ON (T.HD_PRIORITY_ID = P.ID)
 WHERE (S.STATE = 'opened') OR (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) < 7)
 ORDER BY O.FULL_NAME, S.NAME, P.NAME, T.TIME_OPENED
1 Comment   [ + ] Show Comment

Comments

  • Grayematter, So is the queue id the "name" of the queue? if so when I put the statement in the where clause - I am getting a SQL error. I'm not sure where to get the queue id if this is not the name. Thank you for your quick initial answer.
    • Queue_ID is the queue number. If you're not sure what it is, when you hover over your queue from Configuration->Queues, the URL will show up as: http://KBOX/adminui/queue.php?ID=4
    • The other way to get the Queue IDs is to run the query "select ID, NAME from ORG1.HD_QUEUE"
Please log in to comment

Answers

0

Here is the query to include and sort by the Queue names.

SELECT 
 Q.NAME AS 'Queue',
    T.ID AS 'Ticket #',
    T.TITLE AS 'Issue',
    T.TIME_OPENED AS 'Time Opened',
    IFNULL(O.FULL_NAME, 'Unassigned') AS 'Owner',
    U.FULL_NAME AS 'Submitter',
    S.NAME AS 'Status',
    P.NAME AS 'Priority'
FROM
    HD_TICKET T
        JOIN
    HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
        JOIN
    USER U ON (T.SUBMITTER_ID = U.ID)
        LEFT JOIN
    USER O ON (T.OWNER_ID = O.ID)
        JOIN
    HD_PRIORITY P ON (T.HD_PRIORITY_ID = P.ID)
    join
    HD_QUEUE Q on T.HD_QUEUE_ID = Q.ID
WHERE
    (S.STATE = 'opened')
        OR (S.STATE = 'closed'
        AND DATEDIFF(NOW(), T.TIME_CLOSED) < 7)
ORDER BY Q.NAME, O.FULL_NAME , S.NAME , P.NAME , T.TIME_OPENED

If you really want a separate report for each queue, add the line below to the WHERE clause of your original query, replacing "1" with the appropriate queue id.

and T.HD_QUEUE_ID = 1
Answered 04/21/2016 by: grayematter
Fourth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity