/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Report SQL 2 - waiting state

07/18/2016 1123 views

Hi there,

 

I posted last week a request for a service desk report. I wanted to get time worked by owner on tickets every month.

Result below:

 

SELECT CONCAT(YEAR(HD_TICKET.TIME_CLOSED), (" "), LPAD(MONTH(HD_TICKET.TIME_CLOSED),2,'0')) AS Periode, FULL_NAME,

            (SUM(ADJUSTMENT_HOURS * 60) + SUM(TIMESTAMPDIFF(MINUTE,

        HD_WORK.START,

        HD_WORK.STOP))) AS total

FROM

    HD_TICKET

        LEFT JOIN

    HD_CATEGORY ON HD_CATEGORY_ID = HD_CATEGORY.ID

        LEFT JOIN

    HD_WORK ON HD_TICKET.ID = HD_WORK.HD_TICKET_ID

        LEFT JOIN

    USER ON USER.ID = HD_TICKET.OWNER_ID

        LEFT JOIN

    HD_STATUS ON HD_STATUS_ID = HD_STATUS.ID

WHERE

    HD_STATUS.STATE = 'closed'

        AND HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 1 YEAR)

GROUP BY Periode, FULL_NAME

 

I realize after closing my topic that this report count the time the ticket was “on waiting” as it count every step between open and close.

I was wondering if there is an option in the K1000 to not count this time the owner wait for the user to add a comment or anything else?

If not, is it possible to do a report which won’t take into account the time of the state: waiting?

 

This question because I really have no idea about how to do this.

 

I just saw these tables: HD_TICKET_CHANGE and HD_TICKET_CHANGE_FIELD which have timestamp and before_value field. Does these field are ok to do what I need?

 

Finally, if you really don't understood what i'm talking about, do not hesitate to ask again i will try to explain myself differently.

 

Thank you !

1 Comment   [ + ] Show comment

Comments

  • Have you checked out this topic: http://www.itninja.com/blog/view/k1000-service-desk-a-better-average-time-to-close-report

Be the first to answer this question

 
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