WANT: Get a report on $0.00/minute and $0.04/minutes charges for tollfree calls for each server.

Some Queries

Simple server_id and total time

  select server_id, sum(billable_duration) 
    from unbound_cdr
group by server_id

server_id total time at $0.04 rates (during certain timeframe)

  select server_id, sum(billable_duration) 
    from unbound_cdr
   where calldate between '2010-03-30' and '2010-07-26'
group by server_id

No $0.028 charges in this timeframe

select count(*)
 from unbound_cdr
where customer_billed_amount like '0.028'
  and billable_duration < 61
  and calldate between '2010-03-30 07:46:00' 
  and '2010-07-25 22:00:00'
  and (    did like '800%'
        or did like '855%'
        or did like '866%'
        or did like '877%'
        or did like '888%')
;

Result: 0

What they were charged for tollfree versus what they should have been charged for tollfree

Actual charge; Desired charge; Over charge

select server_id, sum(customer_billed_amount) as actual_charge, (sum(billable_duration)/60)*0.028 as desired_charge, (sum(customer_billed_amount)  - (sum(billable_duration)/60)*0.028 ) as over_charge
  from unbound_cdr
 where calldate between '2010-03-30 07:46:00' 
  and '2010-07-25 22:00:00'
  and (    did like '800%'
        or did like '855%'
        or did like '866%'
        or did like '877%'
        or did like '888%')
group by server_id
order by over_charge DESC

Our loss/gain

select sum(over_charge) from (
select server_id, sum(customer_billed_amount) as actual_charge, (sum(billable_duration)/60)*0.028 as desired_charge, (sum(customer_billed_amount)  - (sum(billable_duration)/60)*0.028 ) as over_charge
  from unbound_cdr
 where calldate between '2010-03-30 07:46:00' 
  and '2010-07-25 22:00:00'
  and (    did like '800%'
        or did like '855%'
        or did like '866%'
        or did like '877%'
        or did like '888%')
group by server_id
order by over_charge DESC
) as charges
;

Result: -$4230.07

By this analysis we have undercharged out tollfree customers by ~ $4230.

My tags:

 

Popular tags: