This month, Doug Steele looks at an approach to analyzing telephone logs.
I work at a call center. I have a log table containing information about each call received–when the call started, when it completed, and which agent handled the call. How can I determine the maximum number of agents occupied throughout the day?
This is a classic problem in capacity management: Based on actual usage data, what is the maximum consumption/demand for any resource? In this case, your resource is the calling agents and you want to know the maximum quantity in use at any time. You could substitute maximum number of widgets used or sold in a day/week/month (to determine the maximum quantity that you have to keep in stock), the total bandwidth in use at any one time, the maximum number of users logged into the database, or any other resource/demand. As you’ll see, once you’ve found a way to gather the data, you can determine what your maximum demand is using Access’ built-in functionality.
For the sake of argument, I’ll assume a table named PhoneLog that contains three fields, as shown in Table 1. While you’d probably have some additional fields in the table (such as a Primary Key!), the additional fields aren’t necessary to solve the problem at hand.
Table 1. Description of the PhoneLog table.
|Field name||Field type||Description|
|CallStartDTM||Date||Date/Time at which the call was received|
|CallEndDTM||Date||Date/Time at which the call was completed|
|Agent||Text||Name of the agent handling the call|
In analyzing this sort of problem, I find it easiest to think in terms of when things are changing. If you think about it, it’s pretty obvious that the number of active agents changes any time a call is received or any time a call is completed. That means you can create a query that unions together all of the CallStartDTM and CallEndDTM values in the table. Here’s what that query (which I’ve named qryPhoneLogChanges) would look like:
SELECT CallStartDTM AS CallChange FROM PhoneLogs UNION SELECT CallEndDTM AS CallChange FROM PhoneLogs
As an aside, I assume you realize that Union queries cannot be created in the graphical query designer. You must go into the SQL View of the query and type the SQL yourself.
Once you know when every change occurred, the next thing to do is determine which calls are active at each time change. A call is active if it started at or before the given time and ended after the given time. A call is not active if it ended at exactly the time in question. Now, qryPhoneLogChanges gives a list of times. By joining that list to the PhoneLogs table, it should be possible to determine how many calls are active at each of the times highlighted in qryPhoneLogChanges. There’s a slight complication, though. When you join tables together based on comparison operators other than =, you’re creating what’s referred to as a “non-equijoin query.” As was the case with the Union query, you cannot create non-equijoin queries in the graphical query designer; you have to go into the SQL View of the query and type the SQL, as shown here (which I’ve named qryActiveCalls):
SELECT qryPhoneLogChanges.CallChange, PhoneLogs.Agent, PhoneLogs.CallStartDTM, PhoneLogs.CallEndDTM FROM PhoneLogs RIGHT JOIN qryPhoneLogChanges ON (PhoneLogs.CallStartDTM <= qryPhoneLogChanges.CallChange) AND (PhoneLogs.CallEndDTM > qryPhoneLogChanges.CallChange)
The query qryActiveCalls will return one row for each call that’s active at each of the given times in qryPhoneLogChanges. The next step, therefore, is to determine how many calls are active at any point in time. This is a simple Totals query based on qryActiveCalls and using the Count aggregate function:
SELECT CallChange, Count(Agent) AS ActiveAgents FROM qryActiveCalls GROUP BY CallChange
This is almost what you want. qryActiveCounts returns a list of times and the number of calls active at each time. To see the maximum number of calls active in a given period, you can create another Totals query, this time based on qryActiveCounts and using the Max aggregate function. You’ll need to restrict the value used for grouping in some way. For example, if you want the maximum number of calls per day, you’ll need to use DateValue([CallChange]) in the query:
SELECT DateValue([CallChange]) AS TimeRange, Max(ActiveAgents) AS MaximumActive FROM qryActiveCounts GROUP BY DateValue([CallChange])
Alternatively, you can get the maximum number of calls per hour using a query like this:
SELECT Format([CallChange],"yyyy-mm-dd hh") AS TimeRange, Max(ActiveAgents) AS MaximumActive FROM qryActiveCounts GROUP BY Format([CallChange],"yyyy-mm-dd hh")
It’s essential that you use yyyy-mm-dd hh as the format for the date and hour, in order to get it to sort properly.
Now, I’ve written this solution as four separate queries, basing each query on one that precedes it. This isn’t strictly necessary (well, it is in Access 97, which is what I’ve used for the accompanying downloadable database). It’s possible (in newer versions of Access, or in other DBMSs) to combine all of the SQL into a single query. For example, using Jet 4.0 (Access 2000 and newer), this query is the equivalent of qryMaximumPerHour:
SELECT Format(Q3.CallChange, "yyyy-mm-dd hh") AS TimeRange, Max(ActiveAgents) AS MaximumActive FROM [SELECT Q2.CallChange, Count(Q2.Agent) AS ActiveAgents FROM (SELECT Q1.CallChange, T1.Agent, T1.CallStartDTM, T1.CallEndDTM FROM PhoneLogs AS T1 RIGHT JOIN ( SELECT CallStartDTM AS CallChange FROM PhoneLogs UNION SELECT CallEndDTM AS CallChange FROM PhoneLogs ) AS Q1 ON (T1.CallStartDTM<=Q1.CallChange) AND (T1.CallEndDTM>Q1.CallChange)) AS Q2 GROUP BY Q2.CallChange]. AS Q3 GROUP BY Format(Q3.CallChange, "yyyy-mm-dd hh")
This means that it’s possible to use SQL like this in a pass-through query. The previous solution, which built queries on top of other queries, wouldn’t work with pass-through queries because you can’t base a pass-through query on another pass-through query. With the “queries-on-queries” approach, only qryPhoneLogChanges could be a pass-through query and you’d end up having most of your computation done locally in Jet.
The second part of this Access Answers column appears here Save Yourself Some Work