Case Aging, including “pauses”
Over the years of working with Clarify, I’ve seen a request come up a few times for calculating case aging, including “pauses”.
It’s easy to calculate a total case age. For open cases, it’s the difference between now and the time the case was opened. For closed cases, it’s the difference between case open time and case close time.
But what about the workflows where a case can be paused? For example, the customer may go on vacation, or any other myriad of reasons where we want to pause the clock on the case.
The common approach to this from an application standpoint is to have a new case Status value of Paused (or something similar), so that we simply change the status to Paused to pause the case, and then change it to something else to Un-Pause it.
I’ve seen solutions in the past where business rules are “paused” by manipulating the time_bomb records. For example, check out the Dovetail knowledgebase article: How To Delay Or Pause Business Rules
One thing that was never easy was showing the current case clock, i.e. the amount of time the case has been open, minus any timespans where it was paused.
For example, given a case whose activity log looks like:
- 1:00, Case Created
- 2:00, Case Paused (Status Change)
- 3:00, Case Un-Paused (Status Change)
- 4:00, Case Closed
- 5:00, Case Reopened
- 6:00, Case Closed
Even though we have 5 total hours in play (1:00 –> 6:00), the case clock should only show 3 hours: 1:00 –> 2:00, 3:00 –> 4:00, and 5:00 –> 6:00
We may also want to see what the case clock is at any point in time.
For example, if we looked at the case clock at 3:30, it should show 1.5 hours: 1:00 –> 2:00, and 3:00 –> 3:30
The approach
What’s nice about Clarify/Dovetail is that every event is recorded – who, what, when. But we can have a lot of events for a given case, and calculating all of the time spans between every one of the relevant events every time can end up being a lot. Instead, lets keep a running case clock that we’ll update everytime we stop the clock. When the clock is stopped, we’ll calculate the last timespan, and add it to the stored value. We can simply query this value from the database. When the clock is running, we just need to calculate one timespan – the difference between the current time and the last time the clock was started. Then we can add that to the previously calculated timespan from the database.
Giddy up.
Schema
I created a new table that hangs off the case table via a OTO relation.
Here’s what it looks like in BOLT:
Here’s an example of some records from that table:
Each row has an objid, a flag (integer and string) to indicate if the clock is running or paused, the time_so_far (number of seconds), the last time we calculated the time_so_far value, and if the clock is running, the time the clock was (re-)started.
Starting and Stopping the clock
I created a simple PowerShell script (which uses the Dovetail SDK) that starts and stops the clock.
The interesting part of the code:
if ($action -eq “start”){
#start the clock
if ($isRunning -eq $true){exit;}
$caseClockRow[“is_running”] = 1;
$caseClockRow[“status”] = “running”;
$caseClockRow[“last_started_at”] = Get-date;
}
else{#stop/pause the clock
if ($isRunning -eq $false){exit;}
$caseClockRow[“is_running”] = 0;
$caseClockRow[“status”] = “paused”;
$diff = new-TimeSpan $caseClockRow[“last_started_at”] $(Get-Date);
$caseClockRow[“time_so_far”]+= [int]$diff.TotalSeconds;
$caseClockRow[“last_started_at”] = ‘1/1/1753’;
$caseClockRow[“last_calculated_time”] = Get-date;
}
Starting the clock is pretty easy – set the status to indicate that its running, and then set the last_started_at to Now.
Stopping the clock is a little more complex:
- set the status to indicate that its paused/stopped
- calculate the time on clock so far, which is the previous value of time_so_far + the difference between Now and the last time the clock was started.
- update the timestamp for last_calculated_time to be Now
- set the last_started_date to be the zero date (1/1/1753)
Calling the stop/start script via business rules
We can use business rules to call the start/stop script:
- When the case is created, start the clock
- When the case is closed, stop the clock
- When the case is Reopened, start the clock
- When the case is changed to a paused status, stop the clock
- When the case is changed to a status other than paused, start the clock
BOLT summarizes these business rules:
Rulemanager will fire those business rules which will call the start/stop script..
Alternative approach #1
Rather then Powershell scripts, we could have built this functionality into a message handler in Dovetail Carrier, and had Rulemanager publish a message into the Carrier queue. This would have cut down the overhead of having a new Powershell process start up and connect to the database every time. We would also have better error handling and guaranteed message delivery.
But not everyone has Dovetail Carrier… Yet.
Alternative approach #2
This functionality could also have been built directly in the client application code (such as within the Clarify Classic Client or Dovetail Agent). Might be OK, if you’re only using one client (now and forever), but by firing it based on a rule, this start/stop clock functionality works regardless of the application (Clarify Client, Dovetail Agent, APIs, scripts, web services, etc.) that does the case Create, Close, Reopen, or Status Change.
Calculating the clock elapsed time
As I mentioned earlier, when the clock is stopped, the the time_so_far value will be the correct elapsed time of the clock.
But when the clock is running, we need to do a little calculation, namely the difference between the current time and the last time the clock was started, then add that to the previously calculated timespan from the database (time_so_far).
We could do this from each consumer of this data (Clarify Client, Dovetail Agent, reporting package, etc.)
Or, we can let the database do it for us. That way we just have to do a simple SQL query, and we can get the correct clock time. So lets head down that route.
SQL function
I created a user-defined function in SQL Server for doing this calculation, which returns a number of seconds. I also had this function format it into a string, formatting that number of seconds into days, hours, minutes.
To be more specific, I created a Multi-Statement Table-valued UDF (User Defined Function). That’s a mouth-full! A Multi-Statement Table-Value user-defined function returns a table. Kind of interesting.
I can now select from this function, passing in a case objid as a parameter.
Example
Given this SQL: select * from dbo.GetCaseClock(268435924)
Results in:
SQL View
To make it easy to call this from ClearBasic (and other Clarify specific languages/APIs), I wrapped this call in a SQL View.
Here’s the SQL View as shown in BOLT:
Notice the use of CROSS APPLY, which lets us join a table to a table-valued-function. Cool.
So we get the calculated data from the function, plus the raw data from the case_clock table.
Now, we can make a simple SQL query.
SQL: select * from table_case_clock_view where case_objid = 268435924
Results in:
Cool.
Now all I have to do is make this one simple query, and I get the actual, calculated case clock info, including a number of seconds (elapsed_time_in_seconds), as well as a nicely formatted display string (elapsed_time_display).
This makes it super easy to make this one SQL call from anywhere, and get the latest clock info.
User Interface
Now lets make this SQL query from a client, and display the info.
I added two labels on the case form. One to show the elapsed time, and one to show whether the case is running or not.
Then added a bit of ClearBasic code.
First, a subroutine to make that simple query, and display the info on the form:
Sub FillCaseClock()
Dim recCase As Record
Dim recCaseClock As Record
Dim caseObjid As Long
Dim br As New BulkRetrieve
Dim clockList As List
Dim elapsedTime As String
Dim clockStatus As StringSet recCase = Cobj_CASE_OBJ.Contents
caseObjid = recCase.GetField(“objid”)clockStatus = DEFAULT_CLOCK_STATUS
br.SimpleQuery 0, “case_clock_view”
br.AppendFilter 0, “case_objid”, cbEqual, caseObjid
br.RetrieveRecords
Set clockList = br.GetRecordList(0)
If clockList.Count > 0 Then
Set recCaseClock = clockList.ItemByIndex(0)
elapsedTime = recCaseClock.GetField(“elapsed_time_display”)
clockStatus = CLOCK_LABEL_PREFIX & Space$(1) & recCaseClock.GetField(“status”)If recCaseClock.GetField(“is_running”) = 1 Then
lbl_clock_is.ForeColor = “Running”
Else
lbl_clock_is.ForeColor = “Paused”
End IfEnd If
lbl_clock_elapsed_time_display.Caption = elapsedTime
lbl_clock_is.Caption = clockStatusEnd Sub
Then, I slapped a timer on the form, and had it automatically update that information every 30 seconds:
Sub Form_Load()
Me.DoDefault
Call FillCaseClock
TIMER.Interval = CLOCK_REFRESH_RATE
End SubSub timer_Timer()
Call FillCaseClock
End Sub
When the clock is running, we see:
When the clock is paused, we see:
Sweet!
What about existing cases?
So what do we do about existing cases? Depending on your business needs, you have options.
Typically this type of customization is used for SLA tracking. We probably don’t care about past closed cases. We might care about existing open cases. So we could go through all of the current open cases, calculate the time so far, and then add a case_clock row for each of those with the current data.
Or, just don’t worry about them. After a relatively short period of time, those will all be closed anyway, so don;t sweat those during this near term period. If this is the choice, it might be useful to indicate this on the case form.
What about Oracle?
You’ll notice I used a SQL Server user-defined function, as well as CROSS APPLY. Those won’t work as is in Oracle, but I’m sure Oracle has similar available functions and constructs. I’ll leave this as an exercise for the Oracle-savvy readers.
What about Business Hours? Holidays?
You may have noticed that I only dealt with elapsed time – not business hours. Business Hours add a whole ‘nother level of complexity.
For example, lets say that the case clock should only count time during M-F from 9-5. (Is that 9:00 our time or 9:00 in the customer’s timezone?) Maybe it should exclude holidays. And so forth…
I just punted on business hours. If you need this, and you want to only track business hours, have at it! I’d love to see someone run with this. Fork my code, and go for it. We also do paid custom development work (hint, hint)!
Code
The schemascript, SQL function, and ClearBasic code for the case form (form 776) are in my CaseClock repo on Github.
The UpdateCaseClock.ps1 script is in my powershell repo on Github.
This all sounds familiar. Has this been done before?
Probably. Back when I was part of Clarify Professional Services, I think some of the Clarify PSO guys in the UK wrote something like this.
And back when Cimbridge Software was still around, they had a product called TrueAge that did something similar. I think it used a cbbatch process. They actually presented this at our First Choice Connections conference back in 2002. The Wayback Machine gives some details. But no code.
Ah, the days before blogs and Github, when code was written, not shared, and subsequently lost.
Summary
This was an interesting little project. As I showed above, there are multiple ways to solve this challenge. Probably even better ways than what I’ve done. But hopefully this gives you some ideas on how to tackle this.
And like I said – if anyone wants to tackle Oracle support or business hours, I’d love to see a fork or a pull request!
Hope you find this useful.
Rock on.
