help with rate calculation
Moderator: General Moderators
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)do you know where i can look up syntax on lookup?
i am trying google now?
jason
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Syntax on lookup? a lookup = a seperate select in this case..
something like: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.
Code: Select all
SELECT DISTINCT(destination), name FROM rates ORDER BY destination DESC