Welcome to

Welcome to!

This site was created as a place to share stories, tips, and troubleshooting help with ShoreTel/Mitel systems. ShoreTel/Mitel is obviously the MOST exciting VoiP platform on the market right now, and we realized there was no centralized place to discuss this platform, but now there is. Please feel free to join and share your experiences.

Please Note: This site IS NOT owned, funded, or managed by ShoreTel/Mitel, Inc. although you may find ShoreTel/Mitel employees sharing there experiences and expertise. If you would like more information on ShoreTel/Mitel systems, contact BTX at [email protected]

As always please support the advertisers that help support our site.

Thank You,
See more
See less
  • Filter
  • Time
  • Show
Clear All
new posts

  • Reports / Call stats

    Not sure if this is the correct place for this or not but I am in need of some assistance/advise. I have an sql script I run to see call stats however out of an 8 hour day the possibility for someone to be on the phone for 14 hours is very very slim.

    SELECT call.Extension as partyid,
    AVG(minute(call.duration)) as avgtime,
    COUNT(connect.ID) as cnt,
    SUM(minute(call.duration)) as ttltime
    FROM queuecall JOIN connect ON connect.ID=queuecall.ConnectTableID
    JOIN ON call.ID=connect.CallTableID
    WHERE connecttime BETWEEN '2009-08-04 00:00:00' AND '2009-08-04 23:59:59'
    AND talktimeseconds > 20 GROUP BY call.extension

    When I run Crystal Reports I can see the duplicated calls causing the inaccurate data. My question is this, is the WAN setting set up incorrectly (that is the only thing that is consistent with the duplicated calls) or is ShoreTel just messing up(slim but possible?). Below is an example of what the crystal reports says.

    This happened when we were at 7.? and is still here when we updated to 8.1
    Attached Files

  • #2
    You realize that inner joins do m*n rows? So if there are 5 rows in one table that match 1 row in the other table, you will get 5 results. Now when you do your sum on call.duration, you get 5 times the call's real duration.


    • #3
      Interesting. I will try to find another way to do the SQL query then. However, how would that effect the crystal reports?