Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2012
    Posts
    3

    Unanswered: Query that calculates the number of simultaneous calls

    I have a table that has the start date/time, end date/time and duration of a call. I would like to run a query that gives me the number of simultaneous calls in a particular hour. Can anyone help me create this query?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You would need to clarify what you mean by simultaneous calls? Do you mean calls that started within an hour, or that ended in an hour or had some time within an hour counted. For instance, I start a call at 11:59 which goes on until 12:01. Is this 1 call at 11 and 1 call at 12?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Oct 2012
    Posts
    3
    Thank you for your response. Good question. I realize there will be overlap with an hourly or daily query. I have a set of call detail records, and from those records, I'm supposed to determine the average concurrent active calls per system, per hour (at a precision of one minute). If I query 7pm to 8pm, I should see the average concurrent calls for the hour (averaging the concurrent calls for each minute) within that hour (for each system).

    So, I need a way to check for a count of active calls for 7:00-7:01, 7:01-7:02, etc then average those numbers. A call is considered active if the call's time and duration fall within the current minute being checked.

  4. #4
    Join Date
    Oct 2012
    Posts
    3
    Here is an example of the data in the table.
    +---------------------+---------------------+----------+
    | connect_time | disconnect_time | duration |
    +---------------------+---------------------+----------+
    | 2012-10-17 10:00:01 | 2012-10-17 10:05:30 | 329 |
    | 2012-10-17 10:00:03 | 2012-10-17 10:00:32 | 29 |
    | 2012-10-17 10:00:04 | 2012-10-17 10:00:38 | 34 |
    | 2012-10-17 10:00:10 | 2012-10-17 10:05:29 | 319 |
    | 2012-10-17 10:00:10 | 2012-10-17 10:29:19 | 1749 |
    | 2012-10-17 10:00:11 | 2012-10-17 10:00:49 | 38 |
    | 2012-10-17 10:00:12 | 2012-10-17 10:00:44 | 32 |
    | 2012-10-17 10:00:12 | 2012-10-17 10:05:41 | 329 |
    | 2012-10-17 10:00:13 | 2012-10-17 10:00:37 | 24 |
    | 2012-10-17 10:00:15 | 2012-10-17 10:00:38 | 23 |
    | 2012-10-17 10:00:15 | 2012-10-17 10:22:12 | 1317 |
    | 2012-10-17 10:00:16 | 2012-10-17 10:01:32 | 76 |
    | 2012-10-17 10:00:17 | 2012-10-17 10:01:08 | 51 |
    | 2012-10-17 10:00:20 | 2012-10-17 10:00:54 | 34 |
    | 2012-10-17 10:00:21 | 2012-10-17 10:00:59 | 38 |
    | 2012-10-17 10:00:22 | 2012-10-17 10:00:34 | 12 |
    | 2012-10-17 10:00:23 | 2012-10-17 10:00:28 | 5 |
    | 2012-10-17 10:00:25 | 2012-10-17 10:04:09 | 224 |
    | 2012-10-17 10:00:26 | 2012-10-17 10:00:29 | 3 |
    | 2012-10-17 10:00:26 | 2012-10-17 10:22:13 | 1307 |
    +---------------------+---------------------+----------+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •