Query Development: Overlapping with Call Log Data
Posted on 2014-12-19
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!