+ Start a Discussion

Re: Stop calculating Case Age in Business Hours when case is in a pending state.

trigger CalculateWaitTimeInHours on Case (before insert, before update) {
    if (Trigger.isInsert) {
        for (Case updatedCase:System.Trigger.new) {
            updatedCase.Last_Status_Change__c = System.now();
            updatedCase.Hours_In_Wait_Status__c = 0;
            updatedCase.Hours_Not_In_Wait_Status__c = 0;
    } else {

        // get the wait statuses configured on system
        Set<String> waitStatusSet = new Set<String>();
        for (Wait_Status__c waitStatus:[Select Name From Wait_Status__c]) {

        // get the default business hours
        BusinessHours defaultHours = [select Id from BusinessHours where IsDefault=true];

        // get the closed statuses (because at the point of this trigger Case.IsClosed won't be set yet)
        Set<String> closedStatusSet = new Set<String>();
        for (CaseStatus status:[Select MasterLabel From CaseStatus where IsClosed=true]) {

        // for any case where the status is changed, recalc the business hours in the buckets
        for (Case updatedCase:System.Trigger.new) {
            Case oldCase = System.Trigger.oldMap.get(updatedCase.Id);

            if (oldCase.Status!=updatedCase.Status && updatedCase.Last_Status_Change__c!=null) {
                //OK, the status has changed
                if (!oldCase.IsClosed) {
                    //We only update the buckets for open cases

                    //On the off-chance that the business hours on the case are null, use the default ones instead
                    Id hoursToUse = updatedCase.BusinessHoursId!=null?updatedCase.BusinessHoursId:defaultHours.Id;

                    //The diff method comes back in milliseconds, so we divide by 3600000 to get hours.
                    Double timeSinceLastStatus = BusinessHours.diff(hoursToUse, updatedCase.Last_Status_Change__c, System.now())/3600000.0;

                    //We decide which bucket to add it to based on whether it was in a stop status before
                    if (waitStatusSet.contains(oldCase.Status)) {
                        updatedCase.Hours_In_Wait_Status__c += timeSinceLastStatus;
                    } else {
                        updatedCase.Hours_Not_In_Wait_Status__c += timeSinceLastStatus;

                    if (closedStatusSet.contains(updatedCase.Status)) {
                        updatedCase.Case_Age_In_Business_Hours__c = updatedCase.Hours_In_Wait_Status__c + updatedCase.Hours_Not_In_Wait_Status__c;

                updatedCase.Last_Status_Change__c = System.now();

Best Answer chosen by Admin (Salesforce Developers) 

Because all Cases that were created before this trigger was deployed probably have a null value for "Last_Status_Change__c", so the when trigger reaches the line " (oldCase.Status!=updatedCase.Status && updatedCase.Last_Status_Change__c!=null)" it will not pass the condition and none of the contained code will be executed.


The simplest thing to do might be to set up a workflow rule to, whenever on a Case status change, check to see the if Last_Status_Change__c is null. If it is, then set it to System.now()