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'

 

2 Comments   [ + ] Show Comments

Comments

  • This is a great question as i have the same issue and would like a resolution to this.
  • Does this issue resolve? I would like to know the resolution as i have the same issue.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity