Change Request total Onhold duration

[Yashwanth_reddy]

Hi There,

There is requirement, to calculate total On Hold duration of Change Request.

If Change is On hold for X days in Assess state and moved to Authorize state.

In Authorize state change is On hold for Y days and moved to Scheduled State.

In Scheduled state change is On Hold for Z days and moved to Implement state.

Now need to calculate total On Hold duration in one field.

Total Duration = X + Y + Z

Can any one suggest me..

Below is the script I tried using before BR

var changeRequestGR = new GlideRecord('change_request');

var aggregate = new GlideAggregate('change_request');

    changeRequestGR.addQuery('On hold', 'true'); // Change request is on hold

    changeRequestGR.query();

    while (changeRequestGR.next()) {

        var duration = current.u_duration; //new GlideDuration();

        var states = ['assess', 'authorize', 'scheduled', 'implement', 'review']; // Add other states as needed

        for (var i = 0; i < states.length; i++) {

            var state = states[i];

            if (changeRequestGR[state + '_u_duration']) {

                duration.addAggregate(changeRequestGR['SUM', state + '_u_duration']);

            }

        }

        changeRequestGR.setValue('u_duration', duration);

        gs.info('Change request ' + changeRequestGR.number + ' total on-hold duration: ' + duration);

        changeRequestGR.update();

    }

[Dr Atul G- LNG]

Hi @Yashwanth_reddy 

I will suggest 

create 3 SLA with 3 different state & onhold conditions

and at last get a report on one table.

[Yashwanth_reddy]

Hi @Dr Atul G- LNG 

Any other possiblities.

[Dr Atul G- LNG]

According to me and without code this is the only option,. else need to write big code. 

[GlideScript LLC]

Hello,

I think you are on the right track however, you'd need to handle GlideRecord and GlideAggregate separately. Please try with the following revised script,

var aggregate = new GlideAggregate('change_request');
aggregate.addAggregate('SUM', 'assess_u_duration');
aggregate.addAggregate('SUM', 'authorize_u_duration');
aggregate.addAggregate('SUM', 'scheduled_u_duration');
aggregate.addAggregate('SUM', 'implement_u_duration');
aggregate.addQuery('On hold', 'true');
aggregate.query();

while (aggregate.next()) {
    var totalDuration = 0;
    totalDuration += parseInt(aggregate.getAggregate('SUM', 'assess_u_duration'));
    totalDuration += parseInt(aggregate.getAggregate('SUM', 'authorize_u_duration'));
    totalDuration += parseInt(aggregate.getAggregate('SUM', 'scheduled_u_duration'));
    totalDuration += parseInt(aggregate.getAggregate('SUM', 'implement_u_duration'));

    var changeRequestGR = new GlideRecord('change_request');
    changeRequestGR.get(aggregate.sys_id);
    changeRequestGR.setValue('u_total_on_hold_duration', totalDuration);
    changeRequestGR.update();
    gs.info('Change request ' + changeRequestGR.number + ' total on-hold duration: ' + totalDuration);
}

Kindly mark correct or helpful if there's any value in the script above. I'd appreciate it!