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)

Attribution to: Tobias

Possible Suggestion/Solution #1

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 to: Jon Hazan

Possible Suggestion/Solution #2

My approach is to keep track of how many workers are working at any time, and as long as there is at least one, to keep track of the downtime. To this end, I go through all the objects and record all the start and stop datetimes, then put them all in order, sort of like reading through the timecard logs. When status changes from DownTime = FALSE to DownTime = TRUE, the start time is set. When status changes to DownTime = FALSE again, the time for that stint is recorded, and starts over.

EDIT: changed = to += in code.

set<datetime> alldateset = new set<Datetime>();
map<Datetime, list<yourobject>> startdate2objlistmap = new map<Datetime, list<yourobject>>();
map<Datetime, list<yourobject>> stopdate2objlistmap = new map<Datetime, list<yourobject>>();

for (yourobject) o : objectlist)
{
    alldateset.add(o.Start__c);
    alldateset.add(o.End__c);

    //add to list by startdatetime
    if (startdate2objlistmap.containsKey(o.Start__c)) 
        startdate2objlistmap.get(o.Start__c).add(o);
    else
        stopdate2objlistmap.put(o.Start__c, new list<yourobject>{ o });

    //add to list by stopdatetime
    if (stopdate2objlistmap.containsKey(o.End__c)) 
        stopdate2objlistmap.get(o.End__c).add(o);
    else
        stopdate2objlistmap.put(o.End__c, new list<yourobject>{ o });
}   
list<Datetime> allDatetimes = new list<Datetime>(alldateset);
//put datetimes in order asc
allDatetimes.sort();

Integer activeWorkers = 0;
Boolean DownTime = FALSE;
datetime startdatetime;
Double totaldowntime = 0; 

for (Date d : allDatetimes)
{
    //figure out how many workers are on duty as of this moment
    if (startdate2objlistmap.containskey(d))
        activeWorkers += startdate2objlistmap.get(d).size();
    if (stopdate2objlistmap.containskey(d))
        activeWorkers -= stopdate2objlistmap.get(d).size();

    //check to see if this changes us between downtime and uptime
    if (DownTime == FALSE)
    {
        if (activeWorkers > 0)
        {
            DownTime = TRUE;
            startdatetime = d;
        }
    }
    else if (DownTime == TRUE)
    {
        if (activeWorkers == 0)
        {
            DownTime = FALSE;
                    //edited next line to be '+=' instead of '='
            totaldowntime += d - startdatetime;
        }
    }
}

Attribution to: Jeremy Nottingham
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/1442