Announcement

Collapse
No announcement yet.
X
 
  • 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 shorewarecdr.call 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.

    Comment


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

      Comment

      Working...
      X