how to SUM of multiple duration field?

lucky24
Tera Contributor

‎05-05-2022 05:39 AM

Hi Team,

In metrics definition I am getting multiple record when I am changing same value in multiple time (changing value awaiting caller again and again)

find_real_file.png

 

So now I want to sum these duration and have to store in custom field of incident,

I am trying with below code but not get success 

var duration = new GlideDuration(0);
var mi = new GlideRecord("metric_instance");
mi.addQuery("id", current.sys_id);
mi.addQuery("definition", "fc47ec1d2f1341105d0d56e62799b64e");
mi.addQuery("value", "Awaiting Caller");
mi.query();
mi.getRowCount();
// while(mi.next()){
var dur = new GlideDuration();
dur.setValue(mi.getValue('duration'));
duration = duration.add(dur);

// }
current.u_pause_duration= duration.getDisplayValue();
current.u_total_hour=duration;
gs.addInfoMessage(duration.getDisplayValue());
gs.addInfoMessage(mi.getRowCount());
current.update();

 

So how can we achieve it please help me here?

 

please help me here.

 

Preview file
72 KB
  • 1,078 Views
2 REPLIES 2

Mahesh23
Mega Sage

‎05-05-2022 12:10 PM

Hi,

Try below code

var dur = new DurationCalculator();

dur =mi.duration.dateNumericValue();

mi.duration.setDateNumericValue(dur);

Hope this can help you!

0 Helpfuls

ThorS
Tera Expert

‎10-24-2025 09:53 AM - edited ‎10-24-2025 09:56 AM

I was able to run a background script to perform the summary and provide an output; additionally the script will update your base records with the duration for each value in the field being durat(ed?). 

You should be able to use this for your business rule:

(function() {
    // === Configuration ===
    var metricDefSysId = '1234'; // Replace with your metric_definition sys_id
    var baseTableName = 'x_custombasetable'; // your base table name (lowercase)
   
    // Helper function to convert duration string (e.g., '1970-01-12 00:05:59') to seconds
    function durationStringToSeconds(durationStr) {
        if (!durationStr)
            return 0;
       
        var gdt = new GlideDateTime(durationStr);
        var epoch = new GlideDateTime('1970-01-01 00:00:00');
        var diffMs = gdt.getNumericValue() - epoch.getNumericValue();
        return Math.floor(diffMs / 1000);
    }
   
    // Maps to hold data
    var aggregatedData = {};    // { recordSysId: { _record_number: string, state1: durationDays, ... } }
    var uniqueStates = {};      // { stateName: true }
    var unmatchedIds = [];      // to log sys_ids not found in base table
   
    // Query metric_instance for the metric definition
    var gr = new GlideRecord('metric_instance');
    gr.addQuery('definition', metricDefSysId);
    gr.query();
   
    while (gr.next()) {
        var recordSysId = gr.getValue('id'); // Assuming this is sys_id of base record
        if (!recordSysId) {
            gs.info('metric_instance ' + gr.getValue('sys_id') + ' missing id field');
            continue;
        }
       
        // Parse duration string to seconds
        var rawDuration = gr.getValue('duration');
        var durationSeconds = durationStringToSeconds(rawDuration);
       
        // Convert seconds to days (float)
        var durationDays = durationSeconds / 86400;
       
        // Initialize aggregation object if needed
        if (!aggregatedData[recordSysId]) {
            aggregatedData[recordSysId] = {
                _record_number: null
            };
           
            // Get base record by sys_id
            var recGr = new GlideRecord(baseTableName);
            if (recGr.get(recordSysId)) {
                aggregatedData[recordSysId]._record_number = recGr.getValue('number') || 'UNKNOWN_NUMBER';
            } else {
                aggregatedData[recordSysId]._record_number = 'UNKNOWN';
                unmatchedIds.push(recordSysId);
            }
        }
       
        // Use 'value' field for the state
        var stateValue = gr.getValue('value') || 'UNKNOWN_STATE';
        uniqueStates[stateValue] = true;
       
        if (!aggregatedData[recordSysId][stateValue]) {
            aggregatedData[recordSysId][stateValue] = 0;
        }
        aggregatedData[recordSysId][stateValue] += durationDays;
    }
   
    // Build CSV header
    var stateList = Object.keys(uniqueStates).sort();
    var csvHeader = ['Record Number'].concat(stateList.map(function(s){ return '"' + s + '"'; })).join(',');
   
    var csvRows = [];
    csvRows.push(csvHeader);
   
    for (var recId in aggregatedData) {
        if (!aggregatedData.hasOwnProperty(recId)) continue;
        var recData = aggregatedData[recId];
        var row = [];
        row.push('"' + (recData._record_number || '') + '"');
       
        for (var i = 0; i < stateList.length; i++) {
            var st = stateList[i];
            var dur = recData[st] || 0;
            // Round to 2 decimals
            dur = Math.round(dur * 100) / 100;
            row.push(dur);
        }
       
        csvRows.push(row.join(','));
    }
   
    var csvOutput = csvRows.join('\n');
   
    gs.info('=== Duration (in days) per State per Custom Base Record ===');
    gs.info(csvOutput);
   
    if (unmatchedIds.length > 0) {
        gs.info('=== Unmatched sys_ids (not found in base table) ===');
        unmatchedIds.forEach(function(id) {
            gs.info(id);
        });
    }

// === Map state names to base record duration field names ===
// Adjust these mappings to your actual state names and corresponding field names on x_custombasetable
var stateToFieldMap = {
    
    "Work in Progress": "duration_work_in_progress",
    "Complete": "duration_complete"
   


    // Add all your state-to-field mappings here
};

// Now update each base record with summed durations
for (var recSysId in aggregatedData) {
    if (!aggregatedData.hasOwnProperty(recSysId)) continue;
    var recData = aggregatedData[recSysId];

    // Load the base record
    var recGr = new GlideRecord(baseTableName);
    if (recGr.get(recSysId)) {
        // For each state, set the corresponding duration field
        for (var stateName in stateToFieldMap) {
            if (!stateToFieldMap.hasOwnProperty(stateName)) continue;

            var fieldName = stateToFieldMap[stateName];
            var durationDays = recData[stateName] || 0;

            // Update the field with rounded duration (days)
            recGr.setValue(fieldName, Math.round(durationDays * 100) / 100);
        }
        recGr.update();
    }
}

})();




0 Helpfuls