How to calculate the Overall Elapse Time of the SLA?

Question

I am working on the OOTB Milestones and now looking to calculate the Sum of the Elapse Time.

1. How we can calculate the sum of all these Elapse Times using formula fields? Do I need to write some other logic ? As we all know we can not edit/update the CaseMilestones fields.
2. In some cases, I only need to get the overall(End-to-End SLA) Elapse Time. So in this, I have to used Category & sub category value to differentiate from the another SLA's. How can I only get the 1st SLA elapse time ? Do I need to show Total Elapse time when all milestones are completed, right ?

How we can get the Elaspse Time of all Milestone attached to that case using SOQL ? Any guidance around this ?

Answer 1

You can do this upon executing following SOQL

SELECT SUM(ElapsedTimeInMins) 
From CaseMilestone 
WHERE CaseId = '<case id>' 
and IsCompleted = true

Refer CaseMilestone Object definition

Update

You could limit your query to display the first record milestone record. You could also add case attributes (Category and Sub Category) in the filter.

SELECT CaseId, Case.Category__c,
    CompletionDate,
    CreatedDate,
    ElapsedTimeInMins,Id,
    IsCompleted,
    IsDeleted,
    IsViolated,
    MilestoneTypeId,
    MilestoneType.Name, 
    StartDate 
FROM CaseMilestone  
WHERE CaseId='<case id>' AND IsCompleted=true 
AND Case.Category__c = '<my category>' LIMIT 1