Hi,
I've been working on this for quite awhile now and don't seem to get
the proper query.
I have basically 4 tables.
1. Table john_test contains the numbers of the calls.
2. Table john_country contains the country with prefix.
3. Table john_clients contains the clients and their sub_id's
(because a client usually has a subclient, [ex. highway-2,
highway-3]) and their prefix.
4. Table john_test contains the phone numbers.
--------------------------------------------------------------------------------------------------------------------------------
select * from john_client_rate limit 3;
name | country | cali | cana | callrate | dir_id |
trans_id | svc_id | base | incr | client_id
----------+----------------+------+------+----------+--------+----------+--------+------+------+-----------
highway | Afghanistan | | | 0.6212 | 0 |
0 | | 6 | 6 | 4
highway | Albania | | | 0.3945 | 0 |
1 | | 6 | 6 | 4
highway | Bahamas | | | 0.0513 | 0 |
1 | | 6 | 6 | 4
(3 rows)
select * from john_country limit 3;
country | state | prefix | area_code
---------------+-------+----------+-----------
Afghanistan | | 93 |
Aland Islands | | 35818120 |
Albania | | 355 |
(3 rows)
select * from john_clients limit 3;
id | client_id | sub_id | name | prefix | type
----+-----------+--------+----------+---------+--------
80 | 80 | 0 | highway | 71081 | client
80 | 80 | 0 | highway | 7107011 | client
80 | 80 | 0 | highway | 71091 | client
(3 rows)
select * from john_test limit 3;
client_id | name | phonenum | calledphonenum
| phonenumtranslat | direction | duration
----------+---------+----------------------+-------------------------+------------------+-----------+----------
2 | highway | 83863011351927330133 | 20100147011351927330133
| | outbound | 363
2 | highway | 83863011441179218126 | 1943011441179218126
| | outbound | 83
2 | highway | 83863011441179218126 | 20100147011441179218126
| | outbound | 32
(3 rows)
--------------------------------------------------------------------------------------------------------------------------------
What I want to do is to remove the prefix, and retain the number
using the following query:
select
john_test.name,
john_test.gwrxdcdn,
john_test.duration as dur,
john_client_rate.name as name2,
john_client_rate.country,
john_country.prefix,
substring(john_test.gwrxdcdn from length(john_country.prefix)+1) as
strip,
get_duration(john_test.duration::int,john_client_rate.base,john_client_rate.incr)
as realdur
from john_test
left outer join john_client_rate
on (prefix in
(
select prefix from john_client_rate
where john_test.phonenum ~ ( '^' || john_country.prefix)
order by length(john_country.prefix) desc limit '1'
)
)
limit 20;
