Calculate “net” downtime from multiple records with start/end date/time stamps

I have a table of time confirmations of engineers working on an equipment. Downtime for us is defined as the “net” hrs we worked on the system. There could be multiple people working on it at the same time and all the “overlapping” time needs to be removed.
Any ideas / tips how this could be implemented in either a Salesforce Formula or custom APEX code would be highly appreciated!

Here an example:

1)Engineer A    15/10/2012 10:00    15/10/2012 11:00     60     Minutes
2)Engineer B    15/10/2012 9:00     15/10/2012 15:00     360    Minutes
3)Engineer B    15/10/2012 16:00    15/10/2012 18:00     120    Minutes
4)Engineer A    17/10/2012 8:00     17/10/2012 14:00     360    Minutes

Total Work Time: 900 Minutes
Total Downtime:  840 Minutes (as the first record fully overlaps the second)

Answer

Im not sure how you could do this with a formula, but should be perfectly possible with Apex.

The premise below is to get all the records in order and then loop through them. During the loop we are checking against the previous record and seeing if they overlap. If they do they details are combined until a separate record is found. The combined record is added to the List and the cycle starts again.

This is untested and so you’ll probably have to play with it to get it to work:

YourObj objs [] = [SELECT start__c, end__c FROM YourObj__c WHERE <crtieria here> ORDER BY start__c ASC, end__C ASC];

List<YourObj> newlist [] = new List<YourObj>(); //Store results in here

YourObj holder = new YourObj();

for(YourObj obj: objs){
    if(holder.start__c == NULL){
        holder.start__c = obj.start__c;
        holder.end__c = obj.end__c;
    }else{
        if(holder.start__c <= obj.start__c && holder.end__c > obj.start__c){ //Next records start time is within the last record boundries (The first condition isnt entirely necessary but put there to make clear)
            if(holder.end__c < obj.end__c){ //Next records end extends out of the last records boundries
                holder.end__c = obj.end__c;
            }
        }else{
            newlist.add(holder);

            YourObj holder = new YourObj();
            holder.start__c = obj.start__c;
            holder.end__c = obj.end__c;
        }
    }

}

//Add the remaining record
newlist.add(holder);

//At this point the newlist List should contain the records grouped as you need

Attribution
Source : Link , Question Author : Tobias , Answer Author : Jon Hazan

Leave a Comment