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.


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()

    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)


  • 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
  • if (!newEvents.isEmpty()) { is not necessary any longer.
    – sfdcfox
    Aug 21 '18 at 15:11
  • Oh? Why is that?
    – gNerb
    Aug 21 '18 at 15:12
  • 1
    A DML on an empty list no longer count towards governor limits, it's a no-op. Technically, checking if the list is empty first has worse performance than not (it's only a few ms, though, nothing major).
    – sfdcfox
    Aug 21 '18 at 15:15
  • I for some reason thought it would throw an exception but if that's not the case this is great, reduces the load on my little fingers that much more.
    – gNerb
    Aug 21 '18 at 15:17
  • I updated the answer to include a formula field that's a little more complete. Added some comments and added a type filter to the event query
    – gNerb
    Aug 21 '18 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.