Report SQL 2 - waiting state
I posted last week a request for a service desk report. I wanted to get time worked by owner on tickets every month.
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.STOP))) AS total
HD_CATEGORY ON HD_CATEGORY_ID = HD_CATEGORY.ID
HD_WORK ON HD_TICKET.ID = HD_WORK.HD_TICKET_ID
USER ON USER.ID = HD_TICKET.OWNER_ID
HD_STATUS ON HD_STATUS_ID = HD_STATUS.ID
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 !
Be the first to answer this question