SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query for Finding rates for different phone numbers

    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.

  2. #2
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by JayDave View Post
    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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    what happened when you tested it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  5. #5
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How Do I write query about finding rates?

    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.

  6. #6
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •