Query for Finding rates for different phone numbers

[JayDave]

Hi, I have never written this kind of query, and I don't have any idea where to begin. Some help would be appreciate.

What I have: I have a table with country name, country code, city code, and rate per minute, type (international or local).

I want to make a query which displays the total rate based on where they call and how many minute they used.

problems:
1)The problem with this is Some of the country code and city code are anywhere between 1-5 digits. Also, the phone numbers are 7-10 digits.
2) Some country code matches other country code first digit. example. Russia country code = 7, Australia= 715 (these are just examples)
3) Same thing with city codes. example: Russia country code = 7, cities codes in russia = 9, 906, 905, 912.

And I don't want duplicate records.

I did made a query i will post it soon as I find it where I store it and edit this.

[JayDave]

Hi, I have never written this kind of query, and I don't have any idea where to begin. Some help would be appreciate.

What I have: I have a table with country name, country code, city code, and rate per minute, type (international or local).

I want to make a query which displays the total rate based on where they call and how many minute they used.

problems:
1)The problem with this is Some of the country code and city code are anywhere between 1-5 digits. Also, the phone numbers are 7-10 digits.
2) Some country code matches other country code first digit. example. Russia country code = 7, Australia= 715 (these are just examples)
3) Same thing with city codes. example: Russia country code = 7, cities codes in russia = 9, 906, 905, 912.

And I don't want duplicate records.

I did made a query i will post it soon as I find it where I store it and edit this.

SELECT `to`,countrycode,country,destination,
substring(`to`,4+length(countrycode)),
citycode ,
minutes,price,(minutes * price) cost
FROM cdr,International
WHERE INSTR((substring(`to`,4)), International.countrycode) = 1 and
Instr(substring(`to`,4+length(countrycode)), International.citycode) = 1
and type='INTERNATIONAL'

here is what is my current query looks like

[r937]

what happened when you tested it?

[JayDave]

what happened when you tested it?

it worked on 90% of part that i wanted.

other 10% it is giving me problem where there is no city code. only contains country code.

[JayDave]

I made previous topic. Looks like it was confusing so giving it one last final try to explain it better.

I want to find a total price of calls based on different rates * minutes called.

The problem is I have over 100+ different countries each with different country code ( 1-5 digits) and each country with different city code ( again 0-5) digits.

So How do I tell query that this is where city code starts and this is where phone number starts? because like I said each country and city codes have different length of digits.

[tpatel]

what happened when you tested it?

I have the same isssue..
When i test query, it gives only record when it matches countrycode and citycode.
say if i have missing citycode in my database, it will not return record base on countrycode.

Example:
If i have called to 91-265-123456 and 91-123456. and in my rate table i have
two record 91265 and 91.
but query gives result for 91265 only. so if i have missing or not matching citycode. it doesn't work.

[r937]

amazing that you guys are both working on the same problem

well, the thing to do, obviously, is use CASE expressions so that when country code is missing, you output something different than when it is present

[tpatel]

We havn't figure it out, how to do query if we have missing citycode. If we get that query it will be easy to union two queries results.
As said we able to find records which has country code and citi code present.