calculate duration on the past 3 years cases

Kanika4
Tera Contributor

‎05-06-2024 08:04 PM

Hi all,

 

I am writing a fix script to Calulate Duration on the past 3 years case records.
Duration should calculate the difference between the opened date and resolved date.

 

This script does not seems to be working and cant see any errors thrown.

Can anybody help with the below script ?

 

// Create a new GlideRecord for the case table
var gr = new GlideRecord('sn_customerservice_case');
gr.addEncodedQuery('opened_at>=javascript:gs.beginningOfLast2Years()');
gr.setLimit(10);
gr.query();
 
while(gr.next()) {
    var opened = new GlideDateTime(gr.opened_at);
    var resolved = new GlideDateTime(gr.resolved_at);
 
    if (gr.calendar_duration.nil()) {
        var duration = GlideDateTime.subtract(opened, resolved);
        gr.calendar_duration = duration;
gr.setWorkflow(false);
gr.autoSysFields(false);
gr.update();
    }
}
0 Helpfuls
  • 302 Views
1 REPLY 1

Astik Thombare
Tera Sage

‎05-06-2024 08:46 PM - edited ‎05-06-2024 08:53 PM

Hi @Kanika4 ,

 

Could you please try below script . I tried it in my PDI and it is working fine for me -

 

 

// Create a new GlideRecord for the case table
var gr = new GlideRecord('sn_customerservice_case');

// Filter for cases opened in the past 3 years
var threeYearsAgo = gs.beginningOfPeriod('year', -3);
gr.addEncodedQuery('opened_at>= ' + threeYearsAgo);

// Set limit to avoid processing too many records
gr.setLimit(10);

gr.query();

while (gr.next()) {
  // Check if resolved and duration is empty
  if (gr.resolved_at && gr.calendar_duration.nil()) {
    var opened = new GlideDateTime(gr.opened_at);
    var resolved = new GlideDateTime(gr.resolved_at);
    
    var duration = GlideDateTime.subtract(opened, resolved);
    gr.calendar_duration = duration;
    gr.setWorkflow(false);
    gr.autoSysFields(false);
    gr.update();
  }
}
// Create a new GlideRecord for the case table
var gr = new GlideRecord('sn_customerservice_case');

// Filter for cases opened in the past 3 years
var threeYearsAgo = gs.beginningOfPeriod('year', -3);
gr.addEncodedQuery('opened_at>= ' + threeYearsAgo);

// Set limit to avoid processing too many records
gr.setLimit(10);

gr.query();

while (gr.next()) {
  // Check if resolved and duration is empty
  if (gr.resolved_at && gr.calendar_duration.nil()) {
    var opened = new GlideDateTime(gr.opened_at);
    var resolved = new GlideDateTime(gr.resolved_at);
    
    var duration = GlideDateTime.subtract(opened, resolved);
    gr.calendar_duration = duration;
    gr.setWorkflow(false);
    gr.autoSysFields(false);
    gr.update();
  }
}

 

 

AstikThombare_0-1715053999722.png

 

  If my reply helped with your issue please mark helpful 👍 and correct ✔️ if your issue is resolved.

 

                         By doing so you help other community members find resolved questions which may relate to an issue they're having

 

 

Thanks,

 

Astik