How to get records created on the week and current weeks

[DIVI_IT]

Hi, 
How to get records created on the last week and current week, and after that compare then to get the assigned_to difference value from these records? 

table: Incident 

[SN_Learn · Accepted solution]

Hi @DIVI_IT ,

Please have a look at the sample code below:

var lastweek = new GlideRecord('incident');
lastweek.addEncodedQuery("active=true^opened_atONLast week@javascript:gs.beginningOfLastWeek()@javascript:gs.endOfLastWeek()");
lastweek.query();
while(lastweek.next()){
	gs.info('Incident No. '+ lastweek.number + ','+ 'Assigned To : '+ lastweek.assigned_to.name);
}
var lastWeekCount = lastweek.getRowCount();
gs.info("Last week's Total Count: "+ lastWeekCount);


var currentWeek = new GlideRecord('incident');
currentWeek.addEncodedQuery("active=true^opened_atONThis week@javascript:gs.beginningOfThisWeek()@javascript:gs.endOfThisWeek()");
currentWeek.query();
while(currentWeek.next()){
	gs.info('Incident No. '+ currentWeek.number + ','+ 'Assigned To : '+ currentWeek.assigned_to.name);
}
var currentWeekCount = currentWeek.getRowCount();
gs.info("This week's Total Count: "+ currentWeekCount);

var getDiff = gs.info('Difference: '+ (parseInt(currentWeekCount) - parseInt(lastWeekCount)));

Output:

*** Script: Incident No. INC0009001,Assigned To : Bud Richman
*** Script: Incident No. INC0009005,Assigned To : Antony Alldis
*** Script: Last week's Total Count: 2

*** Script: Incident No. INC0007001,Assigned To : Arya Hajarha
*** Script: Incident No. INC0008001,Assigned To : Luke Wilson
*** Script: Incident No. INC0008111,Assigned To : Aqib Mushtaq
*** Script: This week's Total Count: 3

*** Script: Difference: 1

Please mark this as correct answer and helpful if it resolved, or mark this helpful if this help you to reach towards solution.

[AakashG2703 · Accepted solution]

Hi @DIVI_IT,

Hope you are doing well.

Proposed Solution

In order to achieve this task, I personally tried it on my Personal Developer Instance and used "Incident" Table so that you can get the solution as soon as possible. For this, you just need to create a "Background Script" and write script as mentioned below to get the difference and unique records from the last week and this week data.

Background Script:-

thisWeekInc = [];
lastWeekInc = [];
var thisWeekCount = 0;
var lastWeekCount = 0;
var grInc = new GlideRecord('incident');
grInc.addEncodedQuery('sys_created_onONThis week@javascript:gs.beginningOfThisWeek()@javascript:gs.endOfThisWeek()^assigned_toISNOTEMPTY');
grInc.query();
while(grInc.next()){
    thisWeekCount += 1;
	thisWeekInc.push(grInc.assigned_to.getDisplayValue());
    gs.print('Incident: ' + grInc.number + ' and ' + 'Assigned To: ' + grInc.assigned_to.getDisplayValue());
}
gs.print('This Week Inc Count: '+ thisWeekInc.length);
var grInc2 = new GlideRecord('incident');
grInc2.addEncodedQuery('sys_created_onONLast week@javascript:gs.beginningOfLastWeek()@javascript:gs.endOfLastWeek()^assigned_toISNOTEMPTY');
grInc2.query();
while(grInc2.next()){
    lastWeekCount += 1;
	lastWeekInc.push(grInc2.assigned_to.getDisplayValue());
    gs.print('Incident: ' + grInc2.number + ' and ' + 'Assigned To: ' + grInc2.assigned_to.getDisplayValue());
}
gs.print('Last Week Inc Count: '+ lastWeekInc.length);
var finalArr = [];
for (var i=0; i<thisWeekInc.length; i++){
    if (finalArr.indexOf(thisWeekInc[i])===-1){
        finalArr.push(thisWeekInc[i]);
    }
}
for (var j=0; j<lastWeekInc.length; j++){
    if (finalArr.indexOf(lastWeekInc[j]) === -1){
        finalArr.push(lastWeekInc[j]);
    }
}
gs.print('Unique/Difference: ' + finalArr.length);
gs.print('Final List of Assigned To Users without Duplicates: \n' + finalArr);

For your reference, also attaching screenshots of the outputs that will give you better insights of how the scripts are working or the best thing will be to follow the solution and execute the scripts on your instance.

If you find this information/knowledge/solution helpful, Please don't forget to mark my solution and reply as helpful and accepted.

Thanks 🙂

Aakash Garg

ServiceNow Developer

[SN_Learn · Accepted solution]

Hi @DIVI_IT ,

Please have a look at the sample code below:

var lastweek = new GlideRecord('incident');
lastweek.addEncodedQuery("active=true^opened_atONLast week@javascript:gs.beginningOfLastWeek()@javascript:gs.endOfLastWeek()");
lastweek.query();
while(lastweek.next()){
	gs.info('Incident No. '+ lastweek.number + ','+ 'Assigned To : '+ lastweek.assigned_to.name);
}
var lastWeekCount = lastweek.getRowCount();
gs.info("Last week's Total Count: "+ lastWeekCount);


var currentWeek = new GlideRecord('incident');
currentWeek.addEncodedQuery("active=true^opened_atONThis week@javascript:gs.beginningOfThisWeek()@javascript:gs.endOfThisWeek()");
currentWeek.query();
while(currentWeek.next()){
	gs.info('Incident No. '+ currentWeek.number + ','+ 'Assigned To : '+ currentWeek.assigned_to.name);
}
var currentWeekCount = currentWeek.getRowCount();
gs.info("This week's Total Count: "+ currentWeekCount);

var getDiff = gs.info('Difference: '+ (parseInt(currentWeekCount) - parseInt(lastWeekCount)));

Output:

*** Script: Incident No. INC0009001,Assigned To : Bud Richman
*** Script: Incident No. INC0009005,Assigned To : Antony Alldis
*** Script: Last week's Total Count: 2

*** Script: Incident No. INC0007001,Assigned To : Arya Hajarha
*** Script: Incident No. INC0008001,Assigned To : Luke Wilson
*** Script: Incident No. INC0008111,Assigned To : Aqib Mushtaq
*** Script: This week's Total Count: 3

*** Script: Difference: 1

Please mark this as correct answer and helpful if it resolved, or mark this helpful if this help you to reach towards solution.

[DIVI_IT]

That's a great solution, May I ask you to, how to sent an event to users from the difference? 

[AakashG2703 · Accepted solution]

Hi @DIVI_IT,

Hope you are doing well.

Proposed Solution

In order to achieve this task, I personally tried it on my Personal Developer Instance and used "Incident" Table so that you can get the solution as soon as possible. For this, you just need to create a "Background Script" and write script as mentioned below to get the difference and unique records from the last week and this week data.

Background Script:-

thisWeekInc = [];
lastWeekInc = [];
var thisWeekCount = 0;
var lastWeekCount = 0;
var grInc = new GlideRecord('incident');
grInc.addEncodedQuery('sys_created_onONThis week@javascript:gs.beginningOfThisWeek()@javascript:gs.endOfThisWeek()^assigned_toISNOTEMPTY');
grInc.query();
while(grInc.next()){
    thisWeekCount += 1;
	thisWeekInc.push(grInc.assigned_to.getDisplayValue());
    gs.print('Incident: ' + grInc.number + ' and ' + 'Assigned To: ' + grInc.assigned_to.getDisplayValue());
}
gs.print('This Week Inc Count: '+ thisWeekInc.length);
var grInc2 = new GlideRecord('incident');
grInc2.addEncodedQuery('sys_created_onONLast week@javascript:gs.beginningOfLastWeek()@javascript:gs.endOfLastWeek()^assigned_toISNOTEMPTY');
grInc2.query();
while(grInc2.next()){
    lastWeekCount += 1;
	lastWeekInc.push(grInc2.assigned_to.getDisplayValue());
    gs.print('Incident: ' + grInc2.number + ' and ' + 'Assigned To: ' + grInc2.assigned_to.getDisplayValue());
}
gs.print('Last Week Inc Count: '+ lastWeekInc.length);
var finalArr = [];
for (var i=0; i<thisWeekInc.length; i++){
    if (finalArr.indexOf(thisWeekInc[i])===-1){
        finalArr.push(thisWeekInc[i]);
    }
}
for (var j=0; j<lastWeekInc.length; j++){
    if (finalArr.indexOf(lastWeekInc[j]) === -1){
        finalArr.push(lastWeekInc[j]);
    }
}
gs.print('Unique/Difference: ' + finalArr.length);
gs.print('Final List of Assigned To Users without Duplicates: \n' + finalArr);

For your reference, also attaching screenshots of the outputs that will give you better insights of how the scripts are working or the best thing will be to follow the solution and execute the scripts on your instance.

If you find this information/knowledge/solution helpful, Please don't forget to mark my solution and reply as helpful and accepted.

Thanks 🙂

Aakash Garg

ServiceNow Developer

[DIVI_IT]

That's a great solution, May I ask you to, how to sent an event to users from the difference?