/bundles/itninjaweb/img/Breadcrumb_cap_w.png
Hi guys, trying to create a report that can display total tickets created per month also a seperate report that can show total tickets closed per month. I found many reports but they are combined (created and closed) I tried modifying the combined one to have two seperate reports but it failed. I appreciate your help.
2 Comments   [ - ] Hide Comments

Comments

  • I have this code and it is producing error, "Unknown Cloumn T.CUSTM_FIELD_VALUE3" This field does exist in the table T.
    SELECT OPEN.MONTH, OPEN.YEAR, Coalesce(OPEN.OPEN, 0) AS OPENED
    FROM (SELECT date_format(T.CREATED, '%M') AS MONTH, YEAR (T.CREATED) AS YEAR, COUNT(*) AS OPEN
    FROM HD_TICKET T
    GROUP BY MONTH, YEAR
    ORDER BY YEAR, MONTH) OPEN
    WHERE OPEN.YEAR = date_format(curdate(), '%Y') and
    T.CUSTOM_FIELD_VALUE3 = 'Incident'
    ORDER BY YEAR asc, str_to_date(OPEN.MONTH,'%M') asc
  • Thank you Chuck. Will give it ago. I will need to add the where statement to filter ticket type (incident, request) for created and closed
Please log in to comment

Answer this question or Comment on this question for clarity

Answers

1
Here is the simplest report for tickets opened per month:
SELECT YEAR(CREATED), MONTH(CREATED), COUNT(ID)
FROM HD_TICKET
GROUP BY YEAR(CREATED), MONTH(CREATED)
ORDER BY YEAR(CREATED), MONTH(CREATED)
And closed per month:
SELECT YEAR(TIME_CLOSED), MONTH(TIME_CLOSED), COUNT(ID)
FROM HD_TICKET
WHERE TIME_CLOSED != '0000-00-00 00:00:00'
GROUP BY YEAR(TIME_CLOSED), MONTH(TIME_CLOSED)
ORDER BY YEAR(TIME_CLOSED), MONTH(TIME_CLOSED)



Answered 03/07/2018 by: chucksteel
Red Belt

Please log in to comment

Share