Page 1 of 3

help with rate calculation

Posted: Mon Jul 26, 2004 9:58 am
by jglicken

Code: Select all

+-------------------------+ 
| Tables_in_cdrdb         | 
+-------------------------+ 
| cdr                     | 
| rates                   | 
+-------------------------+ 
2 rows in set (0.00 sec) 

mysql> describe cdr; 
+-------------+--------------+------+-----+---------------------+-------+ 
| Field       | Type         | Null | Key | Default             | Extra | 
+-------------+--------------+------+-----+---------------------+-------+ 
| calldate    | datetime     |      |     | 0000-00-00 00:00:00 |       |
| src         | varchar(80)  |      |     |                     |       | 
| dst         | varchar(80)  |      |     |                     |       | 
| billsec     | int(11)      |      |     | 0                   |       | 
| userfield   | varchar(255) |      |     |                     |       | 
+-------------+--------------+------+-----+---------------------+-------+ 
16 rows in set (0.00 sec) 

one row from cdr
 
| 2004-07-16 13:27:25 | jason | 13055551212 | 11 | | 

there are more rows, but these are the pertinant onesl 


mysql> describe rates; 
+-------------+--------------+------+-----+---------+-------+ 
| Field       | Type         | Null | Key | Default | Extra | 
+-------------+--------------+------+-----+---------+-------+ 
| destination | varchar(25)  |      |     |         |       | 
| name        | varchar(250) |      |     |         |       | 
| rate        | varchar(25)  |      |     |         |       | 
| connectCost | varchar(25)  |      |     |         |       | 
+-------------+--------------+------+-----+---------+-------+ 
4 rows in set (0.00 sec) 

one row from rates 

| 1 | Domestic | 00.005 | 00.005 | 


My goal is to look at 'dst' from table cdr and match this on 'destination' from rates. if 

match is found output = calldate, src, dst, name, (billsec * rate + connect cost). 

my first problem is matching dst with destination. all dst with '1%' will have same rate, but 

table will contain country codes of all countries so i need to match dst and destination 

based on first few characters. 

then i need to do the math. 

should i create 1 big table with all information, will this make it easier? 

thank you for your help. 

jason

Posted: Mon Jul 26, 2004 11:00 am
by feyd

Code: Select all

SELECT a.`calldate`, a.`src`, a.`dst`, b.`name`, (a.`billsec` * b.`rate` + b.`connectCost`) `price`
FROM `cdr` a
LEFT JOIN `rates` b
ON a.`dst` = b.`destination`
:?:

almost there!!!!

Posted: Tue Jul 27, 2004 10:28 am
by jglicken
thank you so much, that gets the first part of my problem. I can get all the records based on src from cdr table with this query, but it only matches on exact matches from rates table.

Code: Select all

mysql> select * from rates;
+-------------+------------+--------+-------------+
| destination | name       | rate   | connectCost |
+-------------+------------+--------+-------------+
| 1           | Domestic   | 00.005 | 00.005      |
| 511         | Lima Peru  | 00.020 | 00.020      |
| 1111        | voicemail  | 5      | 5           |
| 1305        | Miami, Fl. | 0.005  | 0.005       |
+-------------+------------+--------+-------------+
4 rows in set (0.00 sec)
I only get a match on voicemail '1111' because that is the exact number from dst on cdr. I need to match '13055551212' with '1305' in rates?

thank you in advance for your help.

Jason

Posted: Tue Jul 27, 2004 11:00 am
by feyd
you'll need to switch to a LIKE or REGEXP matching then..

http://dev.mysql.com/doc/mysql/en/Strin ... tions.html

still not getting result from select!

Posted: Tue Jul 27, 2004 2:12 pm
by jglicken

Code: Select all

mysql> select a.calldate, a.src, a.dst, b.name, a.billsec, (a.billsec * b.rate + b.connectCost) totalcost from cdr a left join rates b on a.dst = b.destination regexp '^1';
this query returns all rows?
could it be that i am trying to make a match with a 1 character field?

jason

Posted: Tue Jul 27, 2004 2:33 pm
by feyd
I don't think the regexp is doing anything..

almost there!

Posted: Wed Jul 28, 2004 2:09 pm
by jglicken
I can get the results I want matching all '1's from one table with '1' from the other table, but i am really stumped with getting my pattern matching working.

i can get the results individually for the match, but i don't know how to get the join to get me the results i need like '511' = '511*' ?

Code: Select all

mysql> select substring(dst,4) from cdr where dst like '011%' and src='erick';
+------------------+
| substring(dst,4) |
+------------------+
| 5114220165       |
| 584148681837     |
| 584142869237134  |
| 584142869237134  |
| 582869237134     |
| 582869237134     |
| 582869237134     |
| 582869237134     |
| 582869237134     |
| 5114229804       |
| 582869237134     |
| 5058885694       |
| 5058885694       |
| 5058885694       |
| 5058885694       |
+------------------+
15 rows in set (0.06 sec)

mysql> select * from rates where destination regexp '^ї2-9]';
+-------------+------------------+------+-------------+
| destination | name             | rate | connectCost |
+-------------+------------------+------+-------------+
| 511         | Peru Lima        | 0.1  | 0.05        |
| 58414       | Venezuela Mobile | 0.2  | 0.05        |
+-------------+------------------+------+-------------+
2 rows in set (0.00 sec)
this is what i have so far, but it always matches with my destination '1'.

Code: Select all

mysql> select a.calldate, a.src, b.destination, a.dst, substring(a.dst,4), b.name, sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate + b.connectCost), 2) totalcost from cdr a left join rates b on a.dst regexp '^011' = b.destination where dst regexp '^011' and src='erick' order by 'calldate' desc;
+---------------------+-------+-------------+--------------------+--------------------+--------------+----------+-----------+
| calldate            | src   | destination | dst                | substring(a.dst,4) | name         | billsec  | totalcost |
+---------------------+-------+-------------+--------------------+--------------------+--------------+----------+-----------+
| 2004-07-27 10:52:52 | erick | 1           | 0115058885694      | 5058885694         | Domestic USA | 00:00:00 | 0.05      |
| 2004-07-27 10:52:23 | erick | 1           | 0115058885694      | 5058885694         | Domestic USA | 00:00:00 | 0.05      |
| 2004-07-27 10:51:13 | erick | 1           | 0115058885694      | 5058885694         | Domestic USA | 00:00:00 | 0.05      |
| 2004-07-27 10:46:45 | erick | 1           | 0115058885694      | 5058885694         | Domestic USA | 00:00:00 | 0.05      |
| 2004-07-03 13:47:34 | erick | 1           | 011582869237134    | 582869237134       | Domestic USA | 00:00:48 | 0.09      |
| 2004-07-03 13:46:01 | erick | 1           | 0115114229804      | 5114229804         | Domestic USA | 00:01:50 | 0.14      |
| 2004-06-30 00:10:42 | erick | 1           | 011582869237134    | 582869237134       | Domestic USA | 00:19:16 | 1.01      |
| 2004-06-29 23:50:15 | erick | 1           | 011582869237134    | 582869237134       | Domestic USA | 00:00:51 | 0.09      |
| 2004-05-09 00:11:40 | erick | 1           | 011582869237134    | 582869237134       | Domestic USA | 00:03:18 | 0.22      |
| 2004-04-17 13:52:56 | erick | 1           | 011582869237134    | 582869237134       | Domestic USA | 00:01:44 | 0.14      |
| 2004-04-17 13:50:20 | erick | 1           | 011582869237134    | 582869237134       | Domestic USA | 00:00:00 | 0.05      |
| 2004-04-17 13:48:21 | erick | 1           | 011584142869237134 | 584142869237134    | Domestic USA | 00:00:00 | 0.05      |
| 2004-04-17 13:45:23 | erick | 1           | 011584142869237134 | 584142869237134    | Domestic USA | 00:00:00 | 0.05      |
| 2004-04-16 18:51:01 | erick | 1           | 011584148681837    | 584148681837       | Domestic USA | 00:01:16 | 0.11      |
| 2004-04-16 10:23:00 | erick | 1           | 0115114220165      | 5114220165         | Domestic USA | 00:00:03 | 0.05      |
+---------------------+-------+-------------+--------------------+--------------------+--------------+----------+-----------+
15 rows in set (0.07 sec)
thanks in advance,

Jason

Posted: Wed Jul 28, 2004 2:33 pm
by feyd
a.dst regexp '^011' will return true (1) or false(0)

ok

Posted: Wed Jul 28, 2004 2:36 pm
by jglicken
but this one returns what i am looking for

Code: Select all

mysql> select * from rates where destination regexp '^ї2-9]'; 
+-------------+------------------+------+-------------+ 
| destination | name             | rate | connectCost | 
+-------------+------------------+------+-------------+ 
| 511         | Peru Lima        | 0.1  | 0.05        | 
| 58414       | Venezuela Mobile | 0.2  | 0.05        | 
+-------------+------------------+------+-------------+ 
2 rows in set (0.00 sec)
do you have any suggestions on how to put my 2 queries together so destination matches dst and display the correct name?

thanks
Jaosn

Posted: Wed Jul 28, 2004 2:49 pm
by feyd

Code: Select all

select a.calldate, a.src, b.destination, a.dst, substring(a.dst,4), b.name, sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate + b.connectCost), 2) totalcost from cdr a left join rates b on b.destination regexp concat('^', a.dst) where src='erick' order by 'calldate' desc
try that.

Posted: Wed Jul 28, 2004 2:53 pm
by jglicken

Code: Select all

mysql> select a.calldate, a.src, b.destination, a.dst, substring(a.dst,4), b.name, sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate + b.connectCost), 2) totalcost from cdr a left join rates b on b.destination regexp (concat('^', a.dst) where src='erick' order by 'calldate' desc;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'where src='erick' order by 'calldate' desc' at line 1
i get an error?

Posted: Wed Jul 28, 2004 2:57 pm
by jglicken

Code: Select all

mysql> select a.calldate, a.src, b.destination, a.dst, substring(a.dst,4), b.name, sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate + b.connectCost), 2) totalcost from cdr a left join rates b on b.destination regexp (concat('^', a.dst)) where src='erick' and dst like '011%' order by 'calldate' desc;
+---------------------+-------+-------------+--------------------+--------------------+------+----------+-----------+
| calldate            | src   | destination | dst                | substring(a.dst,4) | name | billsec  | totalcost |
+---------------------+-------+-------------+--------------------+--------------------+------+----------+-----------+
| 2004-07-27 10:52:52 | erick | NULL        | 0115058885694      | 5058885694         | NULL | 00:00:00 | NULL      |
| 2004-07-27 10:52:23 | erick | NULL        | 0115058885694      | 5058885694         | NULL | 00:00:00 | NULL      |
| 2004-07-27 10:51:13 | erick | NULL        | 0115058885694      | 5058885694         | NULL | 00:00:00 | NULL      |
| 2004-07-27 10:46:45 | erick | NULL        | 0115058885694      | 5058885694         | NULL | 00:00:00 | NULL      |
| 2004-07-03 13:47:34 | erick | NULL        | 011582869237134    | 582869237134       | NULL | 00:00:48 | NULL      |
| 2004-07-03 13:46:01 | erick | NULL        | 0115114229804      | 5114229804         | NULL | 00:01:50 | NULL      |
| 2004-06-30 00:10:42 | erick | NULL        | 011582869237134    | 582869237134       | NULL | 00:19:16 | NULL      |
| 2004-06-29 23:50:15 | erick | NULL        | 011582869237134    | 582869237134       | NULL | 00:00:51 | NULL      |
| 2004-05-09 00:11:40 | erick | NULL        | 011582869237134    | 582869237134       | NULL | 00:03:18 | NULL      |
| 2004-04-17 13:52:56 | erick | NULL        | 011582869237134    | 582869237134       | NULL | 00:01:44 | NULL      |
| 2004-04-17 13:50:20 | erick | NULL        | 011582869237134    | 582869237134       | NULL | 00:00:00 | NULL      |
| 2004-04-17 13:48:21 | erick | NULL        | 011584142869237134 | 584142869237134    | NULL | 00:00:00 | NULL      |
| 2004-04-17 13:45:23 | erick | NULL        | 011584142869237134 | 584142869237134    | NULL | 00:00:00 | NULL      |
| 2004-04-16 18:51:01 | erick | NULL        | 011584148681837    | 584148681837       | NULL | 00:01:16 | NULL      |
| 2004-04-16 10:23:00 | erick | NULL        | 0115114220165      | 5114220165         | NULL | 00:00:03 | NULL      |
+---------------------+-------+-------------+--------------------+--------------------+------+----------+-----------+
15 rows in set (0.06 sec)
i was missing a bracket :), but i get NULL results with this query?

Posted: Wed Jul 28, 2004 3:00 pm
by feyd
hmm make try

Code: Select all

select a.calldate, a.src, b.destination, a.dst, substring(a.dst,4), b.name, sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate + b.connectCost), 2) totalcost from cdr a left join rates b on a.dst regexp (concat('^', b.destination)) where src='erick' and dst like '011%' order by 'calldate' desc

very strange

Posted: Wed Jul 28, 2004 3:04 pm
by jglicken

Code: Select all

mysql> select a.calldate, a.src, b.destination, substring(a.dst,4), b.name, sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate + b.connectCost), 2) totalcost from cdr a left join rates b on b.destination regexp (concat('^', b.destination)) where src='erick' and dst like '011%' order by 'calldate' desc;
+---------------------+-------+-------------+--------------------+------------------+----------+-----------+
| calldate            | src   | destination | substring(a.dst,4) | name             | billsec  | totalcost |
+---------------------+-------+-------------+--------------------+------------------+----------+-----------+
| 2004-07-27 10:52:52 | erick | 1           | 5058885694         | Domestic USA     | 00:00:00 | 0.05      |
| 2004-07-27 10:52:52 | erick | 511         | 5058885694         | Peru Lima        | 00:00:00 | 0.05      |
| 2004-07-27 10:52:52 | erick | 58414       | 5058885694         | Venezuela Mobile | 00:00:00 | 0.05      |
| 2004-07-27 10:52:23 | erick | 1           | 5058885694         | Domestic USA     | 00:00:00 | 0.05      |
| 2004-07-27 10:52:23 | erick | 511         | 5058885694         | Peru Lima        | 00:00:00 | 0.05      |
| 2004-07-27 10:52:23 | erick | 58414       | 5058885694         | Venezuela Mobile | 00:00:00 | 0.05      |
| 2004-07-27 10:51:13 | erick | 1           | 5058885694         | Domestic USA     | 00:00:00 | 0.05      |
| 2004-07-27 10:51:13 | erick | 511         | 5058885694         | Peru Lima        | 00:00:00 | 0.05      |
| 2004-07-27 10:51:13 | erick | 58414       | 5058885694         | Venezuela Mobile | 00:00:00 | 0.05      |
| 2004-07-27 10:46:45 | erick | 1           | 5058885694         | Domestic USA     | 00:00:00 | 0.05      |
| 2004-07-27 10:46:45 | erick | 511         | 5058885694         | Peru Lima        | 00:00:00 | 0.05      |
| 2004-07-27 10:46:45 | erick | 58414       | 5058885694         | Venezuela Mobile | 00:00:00 | 0.05      |
| 2004-07-03 13:47:34 | erick | 1           | 582869237134       | Domestic USA     | 00:00:48 | 0.09      |
| 2004-07-03 13:47:34 | erick | 511         | 582869237134       | Peru Lima        | 00:00:48 | 0.13      |
| 2004-07-03 13:47:34 | erick | 58414       | 582869237134       | Venezuela Mobile | 00:00:48 | 0.21      |
| 2004-07-03 13:46:01 | erick | 1           | 5114229804         | Domestic USA     | 00:01:50 | 0.14      |
| 2004-07-03 13:46:01 | erick | 511         | 5114229804         | Peru Lima        | 00:01:50 | 0.23      |
| 2004-07-03 13:46:01 | erick | 58414       | 5114229804         | Venezuela Mobile | 00:01:50 | 0.42      |
now i get 1 result for each entry in my rates table. i think you are on the right track. any ideas?

Posted: Wed Jul 28, 2004 3:09 pm
by feyd
copy my entire query.. you didn't copy it right.