Solved

Query Development:  Overlapping with Call Log Data

Posted on 2014-12-19
13
187 Views
Last Modified: 2015-01-15
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!

Todd
0
Comment
Question by:todjklki
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40509936
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.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40509950
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40509991
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.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:todjklki
ID: 40510055
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.
SMDR.xlsx
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40510102
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.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40510208
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?
0
 
LVL 18

Expert Comment

by:Simon
ID: 40510252
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.

SMDR.xlsx

You would probably want to use two separate columns for external and internal calls and then sum them if required.
i.e.
=COUNTIFS(B3:B27,"<" &AI2,G3:G27,"<>" & "INT") in col AJ
=COUNTIFS(B3:B27,"<" &AI2,G3:G27,"=" & "INT")  in col AK
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 250 total points
ID: 40510348
CountIfs might be ok.  Lots of extensions might make for ugly formulas
Anyways,
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.Edit
        rs.Fields(rs1!EXT).Value = 1
        rs.Update
        rs.MoveNext
    Next x
skip:
    rs1.MoveNext
Loop

MsgBox "processed!"

End Sub

Open in new window

Busy.mdb
Busy.xls
0
 
LVL 18

Expert Comment

by:Simon
ID: 40510520
@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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40510855
Good enough, and less complex then!
I run O2003 so countifs aren't an option.
0
 
LVL 18

Accepted Solution

by:
Simon earned 250 total points
ID: 40511190
@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
,(SELECT COUNT(*) 
    FROM SMDR T2
    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
,(SELECT COUNT(*) 
    FROM SMDR T3
    WHERE t3.call_time < (t1.call_time + t1.duration) 
    and (t3.call_time + t3.duration)>t1.call_time 
    AND t3.CALL_TYPE ='INT'
   ) AS INT_Overlaps
FROM SMDR AS T1
ORDER BY T1.CALL_TIME

Open in new window


outputs something like this (I've chopped the datetimes and column titles to make it display on EE):
ID 	STARTTIME 	DURATION 	TYPE 	ENDTIME 	Ext_OL 	Int_OL
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40513367
@SimonAdept
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.

Nick67
0
 

Author Closing Comment

by:todjklki
ID: 40552477
Excellent, everyone.  Thank you!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Viewers will learn how the fundamental information of how to create a table.

805 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question