Hi All

I am trying to create a report on how many tickets were closed in a range of queues per 7 days.
I just want to show a total number of tickets closed as a value.

I have the report of closed tickets by owner working fine but the below report does not give correct values.

SELECT
COUNT(HD_TICKET.ID) AS NUMBER_CLOSED,
date_format(HD_TICKET.CREATED, '%D') AS DAY_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 (HD_TICKET.HD_QUEUE_ID = 1 OR HD_TICKET.HD_QUEUE_ID = 9 OR HD_TICKET.HD_QUEUE_ID = 15 OR HD_TICKET.HD_QUEUE_ID = 6 OR HD_TICKET.HD_QUEUE_ID = 17) AND ((DATE(CURDATE() - INTERVAL 7 DAY) < DATE(HD_TICKET.TIME_CLOSED))) AND (HD_STATUS.NAME = 'Closed')
GROUP BY month(HD_TICKET.CREATED)
ORDER BY month(HD_TICKET.CREATED)

The problem being is that the report that shows all tickets closed per owner, when you add them it shows that 199 tickets were closed but the above report only shows that 169 were closed.
Answer Summary:
Cancel
3 Comments   [ + ] Show Comments

Comments

  • Can you post your report for tickets closed per owner so we can see the difference?
  • Can you post the query you are trying to compare with? That might help with what you are trying to get at.

    I'm also not sure why you are selecting just DAY_OPENED and grouping by month? I would think you would want to select and group by both day and month.
  • Hi,

    Below is what I use for getting closed tickets per owner.

    select HD_TICKET.ID,
    HD_TICKET.TITLE ,
    HD_TICKET.DUE_DATE ,
    DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') as TIME_CLOSED,
    CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
    TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED),
    TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
    DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
    SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED)-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
    '%kh %im')) AS TIME_TO_CLOSE,
    HD_PRIORITY.NAME as PRIORITY,
    HD_CATEGORY.NAME as CATEGORY,
    HD_STATUS.NAME as STATUS,
    HD_IMPACT.NAME as IMPACT,
    MACHINE.NAME as MACHINE_NAME,
    ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
    (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
    from HD_TICKET
    left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
    left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
    left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
    left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
    left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
    where (HD_STATUS.STATE = 'closed') and (HD_TICKET.HD_QUEUE_ID = 1 OR HD_TICKET.HD_QUEUE_ID = 9 OR HD_TICKET.HD_QUEUE_ID = 15 OR HD_TICKET.HD_QUEUE_ID = 6 OR HD_TICKET.HD_QUEUE_ID = 17) and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
    order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
    • You're calculating the date range differently in this rule and the other one. For consistency I would use the same method. The method used here is the one I normally prefer.
Please log in to comment

Answers

1
It took me a few minutes comparing the two queries, but I think I may have found the sources of your frustration.

The first is very minor with the difference in date comparisons.  One query compares a timestamp (with the "NOW()" function) while the other uses a date (with "CURDATE()" function).  This could account for a couple of tickets difference

The second is a bit more devious.  One query filters on HD_STATUS.NAME = 'Closed' while the other filters on HD_STATUS.STATE = 'closed'.  If you have a STATUS other than "Closed" that has a STATE of "closed", you could be throwing off your numbers here.

Take away from this:  If you want the numbers to match, use the same criteria.
Answered 07/07/2014 by: grayematter
Fourth Degree Black Belt

  • Thank you very much for the help, keeping the date function the same and the HD_STATUS.STATE made all the difference, works perfectly now.

    Again thank you for the help.
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share