I would create a business rule to run "After" the record has updated. This will only add the current updated score to the existing score on their user record. See the scheduled job below for how to remove anything previous to 90 days.
Here is the script:
(if you're on ServiceNow Eureka, check the Advanced checkbox)
Insert = True
Update = True
When to run = After
Condition: current.u_failure_score.changes()
Script:
var u_usr = new GlideRecord('sys_user');
u_usr.addQuery('sys_id',current.change_request.assigned_to);
u_usr.query();
if (u_usr.next()){
u_usr.u_failed_change_score += current.u_failure_score;
u_usr.update();
}
Also since you want to remove anything past 90 days old, I believe you should have a daily scheduled job to recalculate the score that runs this script:
var u_usr = new GlideRecord('sys_user');
var u_cTask = new GlideRecord('change_task');
var u_sum = new GlideAggregate('change_task');
//Find all change tasks created in the last 90 days
u_cTask.addEncodedQuery('sys_created_onONLast 90 days@javascript:gs.daysAgoStart(90)@javascript:gs.daysAgoEnd(0)');
u_cTask.query();
while (u_cTask.next()){
var u_at = u_cTask.change_request.assigned_to;
//aggregate the sum of the failure scores grouped by assigned to
u_sum.addAggregate('SUM', 'u_cTask.u_failure_score');
u_sum.groupBy(u_at);
u_sum.query();
while (u_sum.next()){
var total = u_sum.getAggregate('SUM','u_cTask.u_failure_code');
//Here is where you should set that value back to the user record by updating the sys_user table.
//I'm not 100% sure of the code here, but add gs.log statements in the script above to see what
//output you are getting.
//You need something like for each assigned to user, set the u_failed_change_score to be the
//sum aggregate we got from the above query.
