Query Development: Overlapping with Call Log Data

Greetings, Experts!

I have an Access DB that records SMDR call log data from our company’s Avaya system.  (This Access DB is used with a commercial product used to collect the data.)

With that said, the brass has handed me a task that I cannot seem to grasp.  Some folks, internal and external, are complaining that there are many times that they cannot call out or call in.  This naturally gets the attention of the brass.
The Avaya system does not record busy signals, nor data if an internal user fails to get a line out.  The only thing that I’ve got to go on is the data for successful phone calls (in & outbound).

ID   |      Call_Time       |   Duration (s)   |   Call_Type   |   Extension...
1          20141219  11:20:44 AM             127                 Inbound                207
2          20141219  11:21:07 AM             603                 Inbound                208
3          20141219  11:31:12 AM              47                  Inbound                213

To be able to make a decision about how many extra new phone lines to order, it would be nice to know when there is an “overlapping” of more than one line at any time.  

Between [20141219  11:20:44 AM] and [20141219  11:22:20 AM] 4 lines were in use.
Between [20141219  11:21:07 AM] and [20141219  11:24364 AM] 5 lines were in use.
Between [20141219  11:38:41 AM] and [20141219  11:43:17 AM] 4 lines were in use.

Not sure if this is the “best” way of displaying the data or not, as this is my first stab at genning up a solution for the brass.
Experts, what would be an Access Query that would generate this kind of result?

Thank you, thank you, thank you!

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I don't have an Access SQL answer for you, but I have written a SQL Server article called T-SQL: Identify bad dates in a time series, where one of the definitions of 'bad dates' in the big honkin' CTE expression at the bottom of the article is overlapping dates.

Good luck.
Can you post a sample of the SMDR records please. We don't need the sensitive bits (phone and extension numbers) and user names could be anonymised.
I think the SMDR record includes both a start and end time? Not sure about the product you're using. I run a SMDR service that collects the raw format data from our Avaya, but I'm not at work at the moment.
Access Query
I am sure that there'll be folks who will want to stab at that.
I wouldn't hazard a guess about whether you could or not.

I'd code to transform the data with VBA.
What you really want is a table with one record for each second on working time between the start and end of the analysis.
Each record (each second) has a field for each extension.
Each field contains a 1 or a 0 depending on busy or not
You can then sum all the fields in each record for a 'total lines busy' value
You can then kick it out to Excel and graph time against 'total lines busy' for the PHBs.

You'd build the table and code to fill it all with zeroes.
Then for each record like
20141219  11:20:44 AM             127                 Inbound                207
You'd find 2014-12-19  11:20:44 AM  and run through a loop 127 times changing field 207 from zero to 1 and moving to the next record

Could you do that in an Access query?
Hard to say.
Interval data is a bitch.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

todjklkiAuthor Commented:
Thanks for the replies!

An Excel file is attached, Simon, with sample data.  Unfortunately, there is no end time in the source table.  But, this is nothing that can't be handled.

Gotcha on VBA, Nick.  Not exactly sure how to approach that, as I'm a bit rusty.  I think that I understand what you're driving at.
How many extensions, with what numbers?
I've built the first bit, guessing at extensions 210 thru 217

What you really want is a table with one record for each second on working time between the start and end of the analysis.
You'd build the table and code to fill it all with zeroes.
Some fairly unstructured thoughts on this:

1. Do we need to strip out internal calls from the SMDR records - only calls that involve external trunks that need counting, or report on them separately
e.g. (not based on your sample data)
"at 2014-12-14 10:10:13 there were 4 calls using external trunks and 5 internal calls."
"at 2014-12-14 10:10:45 there were 3 calls using external trunks and 2 internal calls."

2. Depending on which Avaya system you have, you might have System Status Monitor or something else that can report peak and current utilisation levels (trunks, voicemail channels etc) and exception counts. I only know the IP Office product, but that definitely displays stats on failed calls  with text like 'no trunk available'.

3. As you're using an external tool to get the SMDR, it may well also pick up the exception log entries?

4. Nick's approach looks workable. I'm guessing you have a far larger number of extensions though? Our IP office handles up to 1000 extensions I think and conference calls for large number of parties.

5. Apart from adding lines, it's worth looking at items like auto-attendant menu systems and voicemail setup, because they use trunks while active. Is the programming of those items sound and hardened against accidental or malicious misuse - e.g. auto-attendant system that can be made to loop endlessly, over-long messages.

6. Are you maxed out on PRI or BRI ISDN or can you easily add VoIP trunks?
Here's a very low-tech Excel solution.
Excel analysis of SMDR file, showing number of external and internal concurrent calls.columns shaded green in the screenshot - I've done text to columns to convert to date/time.
columns shaded orange in the screenshot - columns where I've added formulae.
To do this:
1. Open in Excel
2. Sort by Col B (Call time)
3. Use  Data/text to columns, one column at a time, on col B and then D, using default 'General' to convert string values to date/time.
4. insert formula in col AI, row 2 (=B2+D2) to get end time for each call
5. insert formula in col AJ, row 2 to get the number of subsequent calls that have started before this one finished.
=COUNTIFS(B3:B27,"<" &AI2,G3:G27,"<>" & "INT") & " external and " & COUNTIFS(B3:B27,"<" &AI2,G3:G27,"=" & "INT") & " internal"
Notes: the formula range for the countif should run from one row BELOW the current row to the last row of data on your spreadsheet.


You would probably want to use two separate columns for external and internal calls and then sum them if required.
=COUNTIFS(B3:B27,"<" &AI2,G3:G27,"<>" & "INT") in col AJ
=COUNTIFS(B3:B27,"<" &AI2,G3:G27,"=" & "INT")  in col AK
CountIfs might be ok.  Lots of extensions might make for ugly formulas
Here's a sample in Access with the export to Excel.
There's a couple of tables, a form and a couple of queries
I imported your spreadsheet, and built the table tblSeconds.
The code processes SMDR and toggles fields to 1 from zero for the duration of the call
The one query totals all the extension values.
The second is the query to export data to Excel.

I pasted the data into Excel and built the chart.

Looking now, the sample doesn't have the right code for the processing.
Comparing datetimes is such a pain!
I finally had to coerce them to doubles and round to 6 figures!

Replace  cmdProcess_Click with
Private Sub cmdProcess_Click()
Dim x As Integer
Dim rs As Recordset
Dim TheDate As Date
Dim strCriteria As String
Set rs = CurrentDb.OpenRecordset("select * from tblSeconds;", dbOpenDynaset, dbSeeChanges)

Dim rs1 As Recordset
Set rs1 = CurrentDb.OpenRecordset("select * from SMDR;", dbOpenDynaset, dbSeeChanges)

'walk down rs1
Do Until rs1.EOF
    ' go to the start of the call
    TheDate = CDate(Replace(rs1!Call_Time, Chr(39), ""))
    strCriteria = "Round(CDbl([TheSecond]), 6) = " & Round(CDbl(TheDate), 6)
    rs.FindFirst strCriteria
    If rs.NoMatch = True Then GoTo skip
    'MsgBox rs!TheSecond
    For x = 1 To rs1!Duration_s
        rs.Fields(rs1!EXT).Value = 1
    Next x

MsgBox "processed!"

End Sub

Open in new window

@Nick: Using the spreadsheet method I posted, It's the same number of columns of formulae regardless of the number of extensions, and the formulae themselves are the same as they are not dependent on the number of extensions either.
Good enough, and less complex then!
I run O2003 so countifs aren't an option.
@NIck: Your solution calculates actual concurrency whereas my method just shows the number of overlapping calls.
e.g. for a one hour call starting at 12:00, and several other subsequent short calls between 12:00 and 13:00, mine would count all the subsequent calls as overlapping with the first call, even if they weren't concurrent with each other, so it gives an idea of potential rather than actual concurrency.

Similarly, the query below (which is slightly more accurate than the Excel method because it doesn't just look at subsequent calls) can only report the number of overlapping external and internal calls rather than true concurrency:
SELECT t1.id,t1.call_time as StartTime,t1.duration,t1.call_type, t1.call_time + t1.duration as EndTime
    WHERE t2.call_time < (t1.call_time + t1.duration) 
    and (t2.call_time + t2.duration)>t1.call_time 
    AND t2.CALL_TYPE <>'INT'
   ) AS EXT_Overlaps
    WHERE t3.call_time < (t1.call_time + t1.duration) 
    and (t3.call_time + t3.duration)>t1.call_time 
   ) AS INT_Overlaps

Open in new window

outputs something like this (I've chopped the datetimes and column titles to make it display on EE):
39361 	11:47:48 	00:20:59 	O 	12:08:47 	4 	3
39360 	11:48:01 	00:00:06 	I 	11:48:07 	3 	0
39362 	11:48:01 	00:01:28 	I 	11:49:29 	3 	0
39369 	11:53:49 	00:00:00 	INT 	11:53:49 	1 	0
39370 	12:01:09 	00:00:00 	I 	12:01:09 	1 	0
39371 	12:04:11 	00:01:03 	INT 	12:05:14 	1 	2
39372 	12:04:11 	00:01:03 	INT 	12:05:14 	1 	2

Open in new window

In summary, Nick's method is exact, and might just need modifying to distinguish internal calls from those using trunks. My overlap method may be easier to implement for a broad overview (e.g. looking at weeks or months worth of SMDR data at a time) because it doesn't need all the extensions to be defined in the table, but peak periods  might then need to be examined using Nick's method to establish how many concurrent trunks were used by the overlapping external calls.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
As an Access guy, there are few occasions I can think of that lend themselves to using code to build tables.
This could well be one of them.
We want all the extensions listed in EXT of the SMDR data to be columns in the table.
A guy could calculate concurrency by minute rather than by second with some adjustment to the code.
That'd make for a prettier graph.
Excel 2003 complains that 32000 points are the max for a graph.

todjklkiAuthor Commented:
Excellent, everyone.  Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.