[SalesForce] How to calculate the Overall Elapse Time of the SLA

This is tricky requirement but exactly I have achieved.

Design

1. Create Entitlement Process and define Miletones based on which Case will enter into the milestone. Milestone type will be Independent. You will define single Milestone for this process like Resolution Time.

2. If you have a different SLA based on case types then you need to write code for proper SLA assignment. Let's say Support1 Team's SLA is 24hrs and Support2 team's SLA is 18 hours. So in that scenario, SLA should be dynamic. Use this apex class in Milestone configuration.

code

global class CaseMilestoneTimeCalculator implements Support.MilestoneTriggerTimeCalculator 
{   
    public CaseMilestoneTimeCalculator(){  
    }       

    global Integer calculateMilestoneTriggerTime(String caseId, String milest)      
    {
        //need to query the SLA defined in Custom setting/Meta data type for respective user or group
    }
}

3. Create a separate object say Resolution Tracker which will take the data from Milestone object. This tracker will have all the milestone data related to Case. Take all the fields from Milestone object to create this object and lookup to CaseId. And insert or update record based on case status changes.

4. Since you have to complete one milestone when case status changes so, before starting a new milestone, update previous milestone with completed date.

code

public class MilestoneUtils 
{
    public static void completeMilestone(List<Id> caseIds, DateTime complDate) 
    {

        List<CaseMilestone> cmsToUpdate = [select Id, completionDate
                                            from CaseMilestone cm
                                            where caseId in :caseIds and IsCompleted =False
                                            and completionDate = null limit 1];
            if (cmsToUpdate.isEmpty() == false)
            {
                for (CaseMilestone cm : cmsToUpdate)
                {
                    cm.completionDate = complDate;
                }
            update cmsToUpdate;
            }
    }
}

Flow

Outcome

how can we check if all milestones are completed on that case ?

You could query and check isCompleted values

SELECT CaseId,
    CompletionDate,
    CreatedDate,
    ElapsedTimeInMins,Id,
    IsCompleted,
    IsDeleted,
    IsViolated,
    MilestoneTypeId,
    MilestoneType.Name, 
    StartDate 
FROM CaseMilestone  
WHERE CaseId='<case id>'

How can we calculate the Total ElapseTime and show it in the Custom Field on the case layout ? Do I need to used formula field or the trigger logic ?

You need to trigger an after update trigger, when the status is getting completed then fire this query and update that custom field.

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

I have End-to-end SLA and some SLA within the main SLA. In order to calculate the ElapseTime of the SLA, I only need to consider the main SLA (not other SLA/Sub SLA which are running with main SLA). How can we differentiate those SLA based on Category & SubCategory ? Do I need to write whole logic trigger ?

Yes, you need to write logic in trigger. To retrieve milestones based on Category and SubCategory use this query:

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>'