How to track the age of a case spent in different status value?

Question

I've to create a report on the case object. I've to display how much time a case has spent in a particular status. I've around 6 status values such as Initiated, Waiting for Approval, On Hold, Closed etc., I want to determine how much time a case has spent in each of the status values?

For ex: I have a case 0010. The case age is 10 days, it is in Waiting approval stage for 4 days. I want to track the number of days it has spent in the current status. I tried case life-cycle report but that's not what I'm actually I'm looking for.

Answer 1

Every time the stage changes create an activity with a date/time stamp. Stamp the previous task with the new date/time when the new activity is created.

This should get you close:

public void createStatusChangeEvents(Map<Id, Case> updatedCases, Map<Id, Case> oldCases) {
    List<Event> newEvents = new List<Event>();
    List<Event> lastEvents = new List<Event>();

    // Get the most recent activity per case
    updatedCases = new Map<Id, Case>([
        SELECT Id, (
            SELECT Id
            FROM Events
            WHERE Type = 'Status Change'
            ORDER BY CreatedDate DESC
            LIMIT 1
        )
        FROM Case
        WHERE Id IN :updatedCases
    ]);

    for (Case c : updatesCases.values()) {
        // If the status changes
        if (c.Status != oldCases.get(c.Id).Status) {
            // Create a new event
            newEvents.add(new Event(
                StartTime = system.now(),
                WhatId = c.Id,
                CustomStatusTrackingField__c = c.Status
            ));

            // If the case already has an event
            if (c.Events != null && !c.Events.isEmpty()) {
                // Update the existing event
                c.Events[0].EndTime = system.now()
                lastEvents.add(c.Events[0]);
            }
        }
    }

    insert newEvents;
    update oldEvents;
}

last you can create a formula field on event to track the days in that status:

IF(ISBLANK(EndDate), TODAY()-StartDate, EndDate - StartDate)

Notes:

• The field names I used may not be correct
• I used event because it already has start/end date fields but you can use task if you want to add custom fields or a custom object altogether, if your org has them a big object would be great for this purpose