I start with 2 tariffplans :
-+--------------+--------------------------+-------------+ | id | idtariffplan | dialprefix | buyrate | buyrateinitblock | buyrateincrement | rateinitial | initblock | billingblock | connectcharge | disconnectcharge | stepchargea | chargea | timechargea | billingblocka | stepchargeb | chargeb | timechargeb | billingblockb | stepchargec | chargec | timechargec | billingblockc | startdate | stopdate | starttime | endtime | id_trunk | musiconhold | id_outbound_cidgroup | rounding_calltime | rounding_threshold | additional_block_charge | additional_block_charge_time | tag | disconnectcharge_after | is_merged | additional_grace | minimal_cost | announce_time_correction | destination | +--------+--------------+------------+---------+------------------+------------------+-------------+-----------+--------------+---------------+------------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+---------------------+---------------------+-----------+---------+----------+-------------+----------------------+-------------------+--------------------+-------------------------+------------------------------+------+------------------------+-----------+------------------+--------------+--------------------------+-------------+ | 214135 | 10 | 1700555 | 0.10000 | 1 | 1 | 0.10000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 06:54:05 | 2021-09-10 06:54:05 | 0 | 10079 | 6 | | -1 | 0 | 0 | 0.00000 | 0 | | 0 | 0 | 0 | 0.00000 | 1.000 | 1700555 | | 214138 | 10 | 17005551 | 0.20000 | 1 | 1 | 0.20000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 06:59:00 | 2021-09-10 06:59:00 | 0 | 10079 | 6 | | -1 | 0 | 0 | 0.00000 | 0 | NULL | 0 | 0 | 0 | 0.00000 | 1.000 | 17005551 | | 214139 | 10 | 170055511 | 0.12000 | 1 | 1 | 0.12000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 07:00:24 | 2021-09-10 07:00:24 | 0 | 10079 | 6 | | -1 | 0 | 0 | 0.00000 | 0 | NULL | 0 | 0 | 0 | 0.00000 | 1.000 | 170055511 | +--------+--------------+------------+---------+------------------+------------------+-------------+-----------+--------------+---------------+------------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+---------------------+---------------------+-----------+---------+----------+-------------+----------------------+-------------------+--------------------+-------------------------+------------------------------+------+------------------------+-----------+------------------+--------------+--------------------------+-------------+ 3 rows in set (0.00 sec)
mysql> select * from cc_ratecard where idtariffplan=11; +--------+--------------+------------+---------+------------------+------------------+-------------+-----------+--------------+---------------+------------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+---------------------+---------------------+-----------+---------+----------+-------------+----------------------+-------------------+--------------------+-------------------------+------------------------------+------+------------------------+-----------+------------------+--------------+--------------------------+-------------+ | id | idtariffplan | dialprefix | buyrate | buyrateinitblock | buyrateincrement | rateinitial | initblock | billingblock | connectcharge | disconnectcharge | stepchargea | chargea | timechargea | billingblocka | stepchargeb | chargeb | timechargeb | billingblockb | stepchargec | chargec | timechargec | billingblockc | startdate | stopdate | starttime | endtime | id_trunk | musiconhold | id_outbound_cidgroup | rounding_calltime | rounding_threshold | additional_block_charge | additional_block_charge_time | tag | disconnectcharge_after | is_merged | additional_grace | minimal_cost | announce_time_correction | destination | +--------+--------------+------------+---------+------------------+------------------+-------------+-----------+--------------+---------------+------------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+---------------------+---------------------+-----------+---------+----------+-------------+----------------------+-------------------+--------------------+-------------------------+------------------------------+------+------------------------+-----------+------------------+--------------+--------------------------+-------------+ | 214140 | 11 | 1700555 | 0.15000 | 1 | 1 | 0.15000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 07:07:37 | 2021-09-10 07:07:37 | 0 | 10079 | 8 | | -1 | 0 | 0 | 0.00000 | 0 | NULL | 0 | 0 | 0 | 0.00000 | 1.000 | 1700555 | | 214141 | 11 | 17005552 | 0.30000 | 1 | 1 | 0.30000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 07:09:22 | 2021-09-10 07:09:22 | 0 | 10079 | 8 | | -1 | 0 | 0 | 0.00000 | 0 | NULL | 0 | 0 | 0 | 0.00000 | 1.000 | 17005552 | | 214142 | 11 | 17005554 | 0.10000 | 1 | 1 | 0.10000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 07:10:53 | 2021-09-10 07:10:53 | 0 | 10079 | 8 | | -1 | 0 | 0 | 0.00000 | 0 | NULL | 0 | 0 | 0 | 0.00000 | 1.000 | 17005554 | +--------+--------------+------------+---------+------------------+------------------+-------------+-----------+--------------+---------------+------------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+---------------------+---------------------+-----------+---------+----------+-------------+----------------------+-------------------+--------------------+-------------------------+------------------------------+------+------------------------+-----------+------------------+--------------+--------------------------+-------------+ One is essentially a customer plan the other a carrier, given that not all fields can be merged by the built in tool (at the time of my writing) I decided that I had to merge the carrier (10) into the customer(11). To my dismay only 1 record was update"Ratecard is successfully merged. 1 Record(s)"
And the resulting customer record looked like this: +--------+--------------+------------+---------+------------------+------------------+-------------+-----------+--------------+---------------+------------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+---------------------+---------------------+-----------+---------+----------+-------------+----------------------+-------------------+--------------------+-------------------------+------------------------------+------+------------------------+-----------+------------------+--------------+--------------------------+-------------+ | id | idtariffplan | dialprefix | buyrate | buyrateinitblock | buyrateincrement | rateinitial | initblock | billingblock | connectcharge | disconnectcharge | stepchargea | chargea | timechargea | billingblocka | stepchargeb | chargeb | timechargeb | billingblockb | stepchargec | chargec | timechargec | billingblockc | startdate | stopdate | starttime | endtime | id_trunk | musiconhold | id_outbound_cidgroup | rounding_calltime | rounding_threshold | additional_block_charge | additional_block_charge_time | tag | disconnectcharge_after | is_merged | additional_grace | minimal_cost | announce_time_correction | destination | +--------+--------------+------------+---------+------------------+------------------+-------------+-----------+--------------+---------------+------------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+---------------------+---------------------+-----------+---------+----------+-------------+----------------------+-------------------+--------------------+-------------------------+------------------------------+------+------------------------+-----------+------------------+--------------+--------------------------+-------------+ | 214140 | 11 | 1700555 | 0.10000 | 1 | 1 | 0.15000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 07:07:37 | 2021-09-10 07:07:37 | 0 | 10079 | 8 | | -1 | 0 | 0 | 0.00000 | 0 | NULL | 0 | 0 | 0 | 0.00000 | 1.000 | 1700555 | | 214141 | 11 | 17005552 | 0.30000 | 1 | 1 | 0.30000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 07:09:22 | 2021-09-10 07:09:22 | 0 | 10079 | 8 | | -1 | 0 | 0 | 0.00000 | 0 | NULL | 0 | 0 | 0 | 0.00000 | 1.000 | 17005552 | | 214142 | 11 | 17005554 | 0.10000 | 1 | 1 | 0.10000 | 1 | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 | 0 | 0 | 0.00000 | 0.00000 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-09-10 07:10:53 | 2021-09-10 07:10:53 | 0 | 10079 | 8 | | -1 | 0 | 0 | 0.00000 | 0 | NULL | 0 | 0 | 0 | 0.00000 | 1.000 | 17005554 | +--------+--------------+------------+---------+------------------+------------------+-------------+-----------+--------------+---------------+------------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+-------------+---------+-------------+---------------+---------------------+---------------------+-----------+---------+----------+-------------+----------------------+-------------------+--------------------+-------------------------+------------------------------+------+------------------------+-----------+------------------+--------------+--------------------------+-------------+ 3 rows in set (0.00 sec)
It looks like only the exact match on dialprefix was even considered.
A BETTER merge might be forth coming... Check for updates here and the github.
PSST you can do this to see if a carrier is profitable for a given call for given customer, no merge required.
SELECT CUST.dialprefix AS 'CUST.dialprefix' , CUST.id AS 'CUST.id' , CUST.idtariffplan AS 'CUST.idtariffplan' , CUST.rateinitial AS 'CUST.rateinitial', CARR.dialprefix AS 'CARR.dialprefix' , CARR.id AS 'CARR.id', CARR.idtariffplan AS 'CARR.idtarrifplan' , CARR.rateinitial AS 'CARR.rateinitial' FROM cc_ratecard CUST JOIN cc_ratecard CARR WHERE CUST.idtariffplan = 11 AND CARR.idtariffplan=10 AND 17005551122 LIKE CONCAT ( CUST.dialprefix , '%' ) AND 17005551122 LIKE CONCAT ( CARR.dialprefix , '%' ) ORDER BY ( CUST.dialprefix ) DESC , LENGTH ( CARR.dialprefix ) DESC LIMIT 1; +-----------------+---------+-------------------+------------------+-----------------+---------+-------------------+------------------+ | CUST.dialprefix | CUST.id | CUST.idtariffplan | CUST.rateinitial | CARR.dialprefix | CARR.id | CARR.idtarrifplan | CARR.rateinitial | +-----------------+---------+-------------------+------------------+-----------------+---------+-------------------+------------------+ | 1700555 | 214140 | 11 | 0.15000 | 170055511 | 214139 | 10 | 0.12000 | +-----------------+---------+-------------------+------------------+-----------------+---------+-------------------+------------------+ 1 row in set (0.00 sec)
|