We need to create a simple report that shows the submitter of a ticket and how many tickets they have submitted. Our management wants to be able to distinguish our potential problem users.

Thanks

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

Jay - give this a shot:

 

SELECT S.FULL_NAME AS SUBMITTER_NAME, COUNT(S.FULL_NAME) as COUNTED FROM HD_TICKET  LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)  group by S.FULL_NAME ORDER BY COUNT(S.FULL_NAME) desc

Here is what it would like like in any mysql tool:

 

 

You may want to filter out "admin" you can do this via this query:

 

SELECT S.FULL_NAME AS SUBMITTER_NAME, COUNT(S.FULL_NAME) as COUNTED FROM HD_TICKET  LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)  where S.FULL_NAME != "admin" group by S.FULL_NAME ORDER BY COUNT(S.FULL_NAME) desc
Answered 01/24/2014 by: Jbr32
Tenth Degree Black Belt

  • I should point out the data above has been altered. We have developed a custom dashboard so the data shown above is anonymous from our system. From your system it should show the full name; e.g. Barker,Bob
  • If you wanted to see tickets created within the last 30 days by submitter you could use this:


    SELECT S.FULL_NAME AS SUBMITTER_NAME, COUNT( S.FULL_NAME ) AS COUNTED
    FROM HD_TICKET
    LEFT JOIN USER S ON ( S.ID = HD_TICKET.SUBMITTER_ID )
    WHERE S.FULL_NAME != "admin"
    AND HD_TICKET.CREATED >= ( CURDATE( ) - INTERVAL 30
    DAY )
    GROUP BY S.FULL_NAME
    ORDER BY COUNT( S.FULL_NAME ) DESC
    • Thanks! This is exactly what we were looking for. Having the interval will definitely help.
      Thanks again,

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

Share