Summary of Total # of Minutes used each day

Discussions about new features or changes in existing features

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

Summary of Total # of Minutes used each day

Postby Plat » Wed Feb 06, 2013 9:49 am

Morning folks...

I'm in need of the quickest and easiest way to gather the Total # of minutes that the dialer used per day. I have LCR setup and running playing with 3 different carriers at the moment (we're about to add 2 more to the database for a total of 5 carriers). I need to get a cost analysis report running daily but finding it very difficult to get an exact number off vicidial reports. I'm not even sure if its correct how i'm doing it now the numbers aren't matching up. First i'll do an outbound report, multiply the total hours by 60 then do an inbound report and do the same. Then add those two numbers up. I'm coming up short by almost 10,000+ minutes. I'm able to know that by going into the 1 carrier we were using before in the past and seeing how many minutes we were billed for lets say 51,000 on January 10th...when I pull the dialer reports and do the math im coming up with 41,000.

Are there any reports that could give me just one flat number or a sql query that wouldn't be too taxing on the server that I could use?

Many thanks and enjoy ur coffee...i sure am enjoying mine :0
| VERSION: 2.6-395a | BUILD: 130221-1736 | Asterisk 1.4.44-vici | Clustered Servers (18) | Sangoma UT50 |
Plat
 
Posts: 36
Joined: Fri Aug 31, 2012 11:44 am
Location: Florida

Re: Summary of Total # of Minutes used each day

Postby DomeDan » Wed Feb 06, 2013 11:00 am

Good evening! I'm having my last cup of coffee for today here :)

I had the task to find out how many minutes had been called here a few weeks ago, and the query went something like this:
Code: Select all
select TRUNCATE(talk_sec/60, 0)+1 as minutes, count(*) as antal
from vicidial_users,vicidial_agent_log
where event_time >= '2013-01-01 23:59:59'
and event_time <= '2013-01-31 00:00:00'
and vicidial_users.user=vicidial_agent_log.user
and pause_sec<65000 and wait_sec<65000 and talk_sec<65000 and dispo_sec<65000
and status is not NULL
group by minutes

the magic is TRUNCATE(talk_sec/60, 0)+1 the result is that a call with the length of 0-59 will be 1
and a call with the length of 60-119 will be 2
because we get charged by every started minute

the list can be put in excel and the total minutes can be calculated
firts column is how many minutes and the second is how many calls with that minute lenght

But as you said, the numbers wont add up, but it wasn't so far away from what other reports were showing,
and I dont know if some providers charges for No Answer and similar too though.
Vicidial Partner. Region: Sweden/Norway.
Does Vicidial installation, configuration, customization, add-ons, CRM implementation, support, upgrading, network-related, pentesting etc. Remote and onsite assistance.
Email: domedan (at) gmail.com
DomeDan
 
Posts: 1226
Joined: Tue Jan 04, 2011 9:17 am
Location: Sweden

Re: Summary of Total # of Minutes used each day

Postby Plat » Thu Feb 07, 2013 9:00 am

Hey domedan... thanks for the attempt! ... i need just a precise breakdown of ALL minutes or seconds per day from inbound & outbound combined into one query or report...i figure a query would be easier
| VERSION: 2.6-395a | BUILD: 130221-1736 | Asterisk 1.4.44-vici | Clustered Servers (18) | Sangoma UT50 |
Plat
 
Posts: 36
Joined: Fri Aug 31, 2012 11:44 am
Location: Florida

Re: Summary of Total # of Minutes used each day

Postby williamconley » Sun Feb 10, 2013 6:25 pm

use the carrier log. it does not care about what happened during the call. just keeps track of result (answer/cancel/congestion) and time.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)


Return to Features

Who is online

Users browsing this forum: No registered users and 24 guests