help with rate calculation

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

if you didn't have the name and destination fields in the query, using distinctrow would produce all the right rows. You'd then need to add the name on your own via a lookup to find the longest string that matches a given number...
jglicken
Forum Newbie
Posts: 22
Joined: Mon Jul 26, 2004 9:58 am

Post by jglicken »

Code: Select all

mysql> select distinctrow a.calldate, a.src, a.dst, 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 substring(a.dst,4) regexp (concat('^', b.destination)) where src='erick' and dst like '011%' order by 'calldate' desc;
+---------------------+-------+--------------------+----------+-----------+
| calldate            | src   | dst                | billsec  | totalcost |
+---------------------+-------+--------------------+----------+-----------+
| 2004-07-27 10:52:52 | erick | 0115058885694      | 00:00:00 | 0.05      |
| 2004-07-27 10:52:23 | erick | 0115058885694      | 00:00:00 | 0.05      |
| 2004-07-27 10:51:13 | erick | 0115058885694      | 00:00:00 | 0.05      |
| 2004-07-27 10:46:45 | erick | 0115058885694      | 00:00:00 | 0.05      |
| 2004-07-03 13:47:34 | erick | 011582869237134    | 00:00:48 | 0.59      |
| 2004-07-03 13:46:01 | erick | 0115114229804      | 00:01:50 | 0.48      |
| 2004-06-30 00:10:42 | erick | 011582869237134    | 00:19:16 | 13.15     |
| 2004-06-29 23:50:15 | erick | 011582869237134    | 00:00:51 | 0.63      |
| 2004-05-09 00:11:40 | erick | 011582869237134    | 00:03:18 | 2.29      |
| 2004-04-17 13:52:56 | erick | 011582869237134    | 00:01:44 | 1.23      |
| 2004-04-17 13:50:20 | erick | 011582869237134    | 00:00:00 | 0.05      |
| 2004-04-17 13:48:21 | erick | 011584142869237134 | 00:00:00 | 0.05      |
| 2004-04-17 13:45:23 | erick | 011584142869237134 | 00:00:00 | 0.05      |
| 2004-04-16 18:51:01 | erick | 011584148681837    | 00:01:16 | 0.91      |
| 2004-04-16 10:23:00 | erick | 0115114220165      | 00:00:03 | 0.06      |
+---------------------+-------+--------------------+----------+-----------+
15 rows in set (2.34 sec)
:) thank you so much.
do you know where i can look up syntax on lookup?
i am trying google now?

jason
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Syntax on lookup? a lookup = a seperate select in this case.. :) something like:

Code: Select all

SELECT DISTINCT(destination), name FROM rates ORDER BY destination DESC
store off the results of that query, then run a comparison using [php_man]preg_match[/php_man]() or [php_man]ereg[/php_man]() against dst from each of the rows of the first one. Stop comparing (the first one's row x) when you find the first destination that matches.. add that destination's name to the first queries row while storing it off onto your final list for display.
Post Reply