I am trying to run a report that shows all ticket numbers specific to the close group criteria as well as showing the average time to close the case . I "almost" have the report I need, however, when I generate the report with the following SQL code, I do not receive all information specific to an owner. Meaning, if an owner has several tickets closed in a specific close group (say 0-1 hour), it will only show one ticket number Vs all the tickets for that specific owner.

 

I am using the following and get the below result:

SELECT

    USER.FULL_NAME,

    (case when

        TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),INTERVAL 1 HOUR) then '0-1 hour'

    when TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL 24 HOUR) then '1-24 hours'

    when TIME_CLOSED>=DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),INTERVAL 24 HOUR) then '>24 hours'

    else 'error' end ) as CLOSE_GROUP,

 

HD_TICKET.ID as 'Ticket Number', CUSTOM_FIELD_VALUE22 as Type, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED,

CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)

div 86400),'d ',

TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id) mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE

 from HD_TICKET

LEFT JOIN USER ON USER.ID=HD_TICKET.OWNER_ID

INNER JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID=HD_STATUS.ID

where

HD_STATUS.NAME='closed'

and TIME_CLOSED<>0

and TIME_CLOSED>'2013-12-31' /*change the start date here*/

and TIME_CLOSED<'2014-01-06' /*change the end date here*/

and HD_TICKET.HD_QUEUE_ID in (15) /*add queue numbers here*/

group by CLOSE_GROUP

I am OK losing the Avg Time To Close if necessary.

Thank you in advance for any assistance! 

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity