/build/static/layout/Breadcrumb_cap_w.png

K1000: Tickets Opened by Month

A quick and easy Report showing the number of Tickets opened each month for the last year (running 12 months not calendar)

SELECT 
COUNT(HD_TICKET.ID) AS NUMBER_OPENED,
date_format(HD_TICKET.CREATED, '%M') AS MONTH_OPENED
FROM
HD_TICKET
JOIN
HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
JOIN
HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
WHERE
((TIMESTAMP(HD_TICKET.CREATED) <= NOW()
AND TIMESTAMP(HD_TICKET.CREATED) >= DATE_SUB(NOW(), INTERVAL 12 MONTH)))
GROUP BY MONTH_OPENED
ORDER BY MONTH_OPENED 
 

And the output looks like this.

 

 


Comments

  • On a related note, if you want to get a ticket count by owner for the current year (listing tickets opened and closed), you can use this.

    John
    _______________________________

    *Title*
    Ticket Count by Owner (Current Year)

    *Report Category*
    Helpdesk (Custom)

    *Description*
    Lists ticket count by owner and status by month for current year.

    *SQL Select Statement*
    SELECT OPEN.OWNER, OPEN.MONTH, OPEN.YEAR, Coalesce(OPEN.OPEN, 0) AS OPENED, Coalesce(CLOSED.CLOSED, 0) AS CLOSED
    FROM (SELECT Coalesce(U.USER_NAME, 'NO OWNER ASSIGNED') AS OWNER, date_format(T.CREATED, '%M') AS MONTH, YEAR (T.CREATED) AS YEAR, COUNT(*) AS OPEN
    FROM HD_TICKET T
    LEFT JOIN USER U ON T.OWNER_ID = U.ID
    GROUP BY OWNER_ID, MONTH, YEAR
    ORDER BY YEAR, MONTH) OPEN
    LEFT JOIN (SELECT Coalesce(U.USER_NAME, 'NO OWNER ASSIGNED') AS OWNER, date_format(T.TIME_CLOSED, '%M') AS MONTH, YEAR (T.TIME_CLOSED) AS YEAR, COUNT(*) AS CLOSED
    FROM HD_TICKET T
    JOIN HD_STATUS S ON HD_STATUS_ID=S.ID and S.STATE ='Closed'
    LEFT JOIN USER U ON T.OWNER_ID = U.ID
    GROUP BY OWNER_ID, MONTH, YEAR
    ORDER BY YEAR, MONTH) CLOSED
    ON (OPEN.MONTH = CLOSED.MONTH AND OPEN.YEAR = CLOSED.YEAR AND OPEN.OWNER = CLOSED.OWNER )
    WHERE OPEN.YEAR = date_format(curdate(), '%Y')
    ORDER BY YEAR desc, str_to_date(OPEN.MONTH,'%M') desc, OWNER

    *Break on Columns*
    MONTH - jverbosk 7 years ago
  • Nice ;) - jdornan 7 years ago
  • (Y) - emorrison 4 years ago
  • how can you have the months sort in order top down? - jrkeister26 3 years ago
This post is locked
 
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