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...
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'
There are no answers at this time