Looking to calculate time spent in a stalled state
We are building our SLA's around the fact that a given ticket could be in a stalled state for any given amount of time based on external factors. In addition, a ticket can go into a stalled state many times throughout it's lifecycle. The standard fields of Time_opened and Time_stalled only track the last time a ticket hit either of those states.
I need to be able to calculate the total time a ticket has spent in a stalled state. I had planned on using the following logic to update a custom field, but I can't update a table that is being used in the from statement...
update HD_TICKET
set HD_TICKET.CUSTOM_FIELD_VALUE5 = (HD_TICKET.CUSTOM_FIELD_VALUE5 + (select TIME_TO_SEC(TIMEDIFF(htc.timestamp, ht.time_stalled))
from HD_TICKET_CHANGE_FIELD htcf, HD_TICKET_CHANGE htc, HD_TICKET ht
where 1 =1
and htcf.hd_ticket_change_id = htc.id
and ht.id = htc.hd_ticket_id
and htcf.after_value = 'Opened'
-
This is a great question as i have the same issue and would like a resolution to this. - chris.poston 9 years ago
-
Does this issue resolve? I would like to know the resolution as i have the same issue. - lovablemeila 8 years ago
Answers (0)
Be the first to answer this question
