I am pleased to announce that we have released an AppExchange application called Case Age In Business Hours (click link for the AppExchange listing) under the Salesforce Labs banner.  In this post I'll explain what it does, how it does it, and how you can modify the code if you so desire to make it do more.

So first, what does it do?  More than its name suggests, actually.  First, and most obviously, it calculates the Case Age In Business Hours for your closed cases.  Fancy that.  It also has another useful function, though: it includes two more fields called Time With Support and Time With Customer that distinguish between time spent by your support team and time spent awaiting the customer.  Note that these new fields will only be accurate for closed cases -- I'll explain why in a bit.

It sounds like it might be complex, doesn't it?  A few years ago, back in my full-time developer days, I wrote some code that calculated time differences in business hours, taking into account time zone differences, daylight savings, and all that jazz.  It was hundreds and hundreds of lines of code, and it took me days to get it right.  My pinkies still twitch at the thought of it.

Thankfully, some of the capabilities we recently added to the Force.com platform make the Case Age In Business Hours package a cinch.  It does everything my old hunk of code did and more, but if you look at the trigger which forms the basis of this package, there are only 55 lines of code, and half the lines are comments or whitespace.

What made it all possible are the BusinessHours methods in Apex, particularly BusinessHours.diff.  You pass BusinessHours.diff two times and a set of Business Hours (and you can define as many sets of Business Hours as you want at Setup->Company Info->Business Hours) -- and it spits back at you the time difference in business hours!  It does all the time zone translation and daylight savings nastiness for you, and all you have to do is call the method.  That's it!

Now for the interesting part, though: how about those Time With Support and Time With Customer fields?  How do we figure that out?

Well, if you install the package, you'll notice that it also installs a custom object and tab called "Stop Statuses."  To set it up, you access this tab and add the names of the case statuses you'd use when you're waiting for your customer.  Some people have "Awaiting Customer Response" or "Awaiting Customer Validation" -- some just have "On Hold."  Whatever your statuses may be, add them all in the Stop Statuses section.  Then, whatever time your case spends in one of those statuses will be tallied to the Time With Customer field -- and any time your case spends not in a stop status (and not in a closed status, of course) will be added to to the Time With Support field.

Now what if you want to take it to the next level -- let's say you want to add a bucket and measure Time With Tier 1, Time With Tier 2, and Time With Customer?  Well, that wouldn't be so hard -- you'd just have to factor in the owner as well as the status when deciding what times go in what buckets.

Before we begin, remember that because the Business Hours functionality is new, this Apex will only compile against the 13.0 API and higher (13.0 being the latest as of the Summer '08 release).  If you haven't got the latest Force.com IDE plugin, now would be a wonderful time to download it.  It makes Apex development much easier.

First, we'll have to add some fields to Case to store this bucket. Let's call them Time With Tier 1 and Time With Tier 2 (with corresponding API names Time_With_Tier_1__c and Time_With_Tier_2__c respectively).

Now we'll modify the trigger a little to accommodate these new buckets.  First, we'll need to figure out if the current user is a Tier 1 or a Tier 2 agent.  Let's assume (for the sake of simplicity) that we have two queues, Tier 1 and Tier 2, and that the current case owner will either be in one or the other.  It is admittedly a simplistic example, but this is a blog post, not a book.

All you really need to do is add some code after this line:

Double timeSinceLastStatus = BusinessHours.diff(hoursToUse, updatedCase.Last_Status_Change__c, System.now())/3600000.0;

That's the code that actually figures out how many business hours have passed since the last status change.  At this point, you have the time you need to add to a bucket -- now all you have to do is find the right bucket to add it to.

In the package, the bucketing code is pretty simple -- either the case spent the time since the last status change in a stop status, or it didn't:

if (stopStatusSet.contains(oldCase.Status)) {
    updatedCase.Time_With_Customer__c += timeSinceLastStatus;
} else {
    updatedCase.Time_With_Support__c += timeSinceLastStatus;
}

We'll have to modify this statement to figure out whether the case owner is in tier 1, and then assign the time to the proper bucket.  First, let's find out if the owner in tier 1:

Boolean ownerIsTier1 = false;
//Search the Tier 1 queue for this user
GroupMember groupMember = [Select Id from GroupMember g where g.Group.Name='Tier 1' and 
                           g.UserOrGroupId=:updatedCase.OwnerId];
if (groupMember != null || updatedCase.Owner.Name == 'Tier 1') {
    //We found the user in the Tier 1 queue!
    ownerIsTier1 = true;
}

The variable ownerIsTier1 will now be true if either the owner was found in the Tier 1 queue or if the owner is the Tier 1 queue itself.  Now that we've figured that out, we can put the time in the right bucket:

//We decide which bucket to add it to based on whether it was in a stop status before
if (stopStatusSet.contains(oldCase.Status)) {
    //This still goes in the customer bucket!
    updatedCase.Time_With_Customer__c += timeSinceLastStatus;
} else {
    //This is time with support -- let's see which tier's bucket we put it in
    if (ownerIsTier1) {
        updatedCase.Time_With_Tier_1__c += timeSinceLastStatus;
    } else {
        updatedCase.Time_With_Tier_2__c += timeSinceLastStatus;
    }
}

Finally, we change the Case Age In Business Hours total code a little:

if (closedStatusSet.contains(updatedCase.Status)) {
    updatedCase.Case_Age_In_Business_Hours__c = 
    updatedCase.Time_With_Customer__c + 
    updatedCase.Time_With_Tier_1__c +
    updatedCase.Time_With_Tier_2__c;
}

And there we have it.  Extending this example, you can calculate business hours times for all kinds of buckets.  Just imagine the reporting possibilities?  I can hear call center managers around the globe rubbing their hands together in glee.

Here's the finished trigger modified per the example given here.  I'll leave it to you to write the test cases for it.

A little earlier I noted that these fields are only accurate for closed cases.  Why do you suppose that is?

Back in high school, my creative writing teacher used to say that if you want to hold your reader's attention, you have to leave an unresolved mystery until the end.  Well, if you're still reading this, I guess it worked -- thanks Mrs. T! 

The answer to today's unresolved mystery is that these fields don't measure the difference between the time the case was opened and now -- they measure the difference between the time the case was opened and the last time the case status changed.  That's because only a change to the case can trigger the Apex which updates these fields, and I deemed a status change the most appropriate change to perform this sort of recalculation.  Once the case is closed, these fields are frozen -- even if you change it from one closed state to another, they won't budge (if you reopen the case, on the other hand, they'll start tallying again).  The fact of the matter is, then, that only once the case is closed will you have a final tally of Time With Customer, Time With Support, and Case Age In Business Hours.