How to calculate the total on hold time in a custom table?

Nihar2
Tera Contributor

‎02-07-2023 06:42 AM

In 'csm_service' table I have to calculate the total on hold time and populate the on hold time duration in 'u_total_on_hold_duration' field.

 

u_total_on_hold_duration field type=duration .

I tried to calculate the duration from metric_instance table.

But I am unable to do this, Please help me to do this.

0 Helpfuls
  • 908 Views
1 REPLY 1

Salah Qawaqneh
Mega Guru

‎02-08-2023 12:39 AM

Hi Nihar,

You can calculate the total on-hold time duration for a case by subtracting the "On Hold" start time from the "On Hold" end time. To populate this information, you can create a custom field on the Case (or "Incident") table that will store the on-hold duration.

Here's one approach to calculate and populate the on-hold time duration:

  1. Create a custom field on the table to store the on-hold duration.
  2. Create a business rule on the table that will run when the "On Hold" start and end times are updated.
  3. In the business rule, use the following script to calculate the on-hold duration:

 

var startTime = current.on_hold_start;
var endTime = current.on_hold_end;
var duration = 0;

if (startTime && endTime) {
  duration = endTime - startTime;
}

current.on_hold_duration = duration;

 

Save and activate the business rule.

With this setup, the on-hold duration will be automatically calculated and populated every time the "On Hold" start and end times are updated. 

0 Helpfuls