Calculate call cost from rate table using MySQL

Question

I am trying to compare call rates between two telephone providers. I have two tables, as follows:

CREATE TABLE 18185_rates (
  calldate DATE,
  calltime TIME,
  calledno VARCHAR(20),
  duration INTEGER(8),
  callcost FLOAT(5 , 3 )
);

CREATE TABLE int_rates (
  dialcode VARCHAR(20),
  description VARCHAR(20),
  callcost FLOAT(5 , 3 )
);

The 18185_rates contains call data records from a phone system, some example values as follows:

calldate,calltime,calledno,duration,callcost
2013-07-30,11:21:38,35342245738,10,0.050
2013-07-30,16:19:25,353872565822,37,0.130
2013-08-02,08:31:12,65975636187,1344,0.270
2013-08-05,11:03:53,919311195965,2356,1.640

The table int_rates contains tariff data for calls from another provider in the following format:

dialcode,description,callcost
1,USA,0.012
1204,Canada,0.008
1204131,Canada,0.018
1226,Canada,0.008
1226131,Canada,0.018
1242,Bahamas,0.137
1242357,Bahamas Mobile,0.251
1242359,Bahamas Mobile,0.251

I am trying to run a comparison, so that I can see how much the calls in 18185_rates would have cost with the other provider. I can't work out how to join the two tables based on the variable length dialling code in int_rates.

After some help from @Gordon Linoff below, I've come up with the following code:

SELECT 
    r.*,
    (SELECT permin
     FROM int_rates ir1
     WHERE r.calledno LIKE CONCAT(ir1.dialcode, '%')
     ORDER BY dialcode DESC
     LIMIT 1) AS newcostpermin
FROM
  18185_rates r;

@FilipeSilva sorry I only put a subset of the data as examples, there should be a price for every call from 18185_rates in the int_rate table — btongeorge, Sep 04 '13 at 09:15

Gordon Linoff · Accepted Answer · 2013-09-04T11:26:58.953

I am assuming that you want to match each phone number to the cost that has the longest prefix. Here is an approach:

select ir.*,
       (select callcost
        from int_rates ir
        where r.calledno like concat(ir.dialcode, '%')
        order by length(ir.dialcode) desc
        limit 1
       ) as TheirCost
from 18185_rates r;

This is using a correlated subquery to find the longest dialcode that matches the beginning of the call. This will be NULL if nothing matches. Also, this will not be efficient and cannot use indexes.

EDIT:

There are different ways to approach this. The simplest is just to duplicate the subquery:

select ir.*,
       (select callcost
        from int_rates ir
        where r.calledno like concat(ir.dialcode, '%')
        order by length(ir.dialcode) desc
        limit 1
       ) as TheirCost,
       (select description
        from int_rates ir
        where r.calledno like concat(ir.dialcode, '%')
        order by length(ir.dialcode) desc
        limit 1
       ) as TheirDescription
from 18185_rates r;

In practice, I would pull a primary key out in the first subquery and then join back to the table, to get whatever fields I want from the that table. However, you don't specify the table layout and if the first query has reasonable performance, then doing it twice should be ok too.

Thanks, your assumption is correct about the prefix matching. Think you have a typo on the first line, should it be "select r.*"? — btongeorge, Sep 04 '13 at 08:21
Have added some updates to the question above based on your help - would be interested to hear your thoughts on the last bit of the puzzle! Thx — btongeorge, Sep 04 '13 at 09:36
thanks, that last bit worked fine, bit slower but perfect for my purposes. — btongeorge, Sep 04 '13 at 16:12

Calculate call cost from rate table using MySQL

1 Answers1