Uploaded image for project: 'ASTPP Community'
  1. ASTPP Community
  2. ASTPPCOM-306

Max Channels Used Report (Overlap Counts).

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Done
    • Resolution: Done
    • Affects Version/s: None
    • Fix Version/s: None
    • Labels:
      None

      Description

      Not sure it will work with < 3.0
      But here is a SQL statement that will count call channel overlaps (number of paths in use at one time) by an account. This is ALL channels, IN/OUT it could be modified to show individuals counts as well.

      In order for it to work efficiently, you need to make sure an index on created on callstart and end_stamp. Note: If you database is LARGE it could talk a LONG LONG time for it to index the first time.

      SELECT group_concat(v2.accountid) as Overlaps, count(v2.accountid)+1 as OverlapCount, v1.accountid, v1.callstart, v2.accountid, least(v1.end_stamp,v2.end_stamp) AS end_stamp, v1.uniqueid, v2.uniqueid
      FROM cdrs v1
      JOIN cdrs v2 ON v1.uniqueid <> v2.uniqueid and v1.callstart >= v2.callstart and v1.callstart < v2.end_stamp and v1.accountid = v2.accountid

      group by v1.accountid,
      DATE_FORMAT(
      v1.callstart,'%Y-%m-%d'),
      DATE_FORMAT(
      v1.end_stamp,'%Y-%m-%d')

      ORDER BY v1.accountid, v1.callstart;

      This shows counts by Date.
      '%Y-%m-%d %H') - will show by date/hour
      '%Y-%m-%d %H') - will show by date/hour/minute if you need go that granular

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            countrdd countrdd
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: