How to find best matches for long distance rates based on phone number?

Question

I need to find out the best way to do this - here is an example. Say you want to call England: and the number you want to call is: 447400123456. Now you can use three different long distance providers but they all have difference rates. There is a table of LD rates like this:

VendorID    Dialcode    Description             Rate
------------------------------------------------------
1           44          UK - All                0.01
1           447         UK - Mobile             0.02
2           44          UK - All                0.015
3           44          UK - All                0.013
3           44740       UK - Mobile - Hutch     0.01

I need a query that can match and return the maximum digits for the phonenumber for each vendor - so for the number above it should return:

VendorID    Dialcode    Description             Rate
------------------------------------------------------
1           447         UK - Mobile             0.02
2           44          UK - All                0.015
3           44740       UK - Mobile - Hutch     0.01

I hope there is something that can be done besides just grinding down the number with a loop. Also if there should be an index on the table to speed up processing - what would you suggest?

Answer 1

You can use ROW_NUMBER() OVER(PARTITION BY VendorID ORDER BY LEN(DialCode) to get the maximum number of digits for each VendorID:

SQL Fiddle

DECLARE @telNo VARCHAR(50) = '447400123456';
WITH Cte AS(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY VendorID ORDER BY LEN(DialCode) DESC)
    FROM tbl
    WHERE @telNo LIKE DialCode + '%'
)
SELECT
    VendorID, DialCode, Description, Rate
FROM Cte
WHERE RN = 1

RESULT:

| VendorID | DialCode |         Description |  Rate |
|----------|----------|---------------------|-------|
|        1 |      447 |         UK - Mobile |  0.02 |
|        2 |       44 |            UK - All | 0.015 |
|        3 |    44740 | UK - Mobile - Hutch |  0.01 |

Answer 2

You can do this as:

select rt.*
from (select rt.*,
             row_number() over (partition by vendorid order by rate desc) as seqnum
      from ratestable rt
      where '447400123456' like dialcode + '%'
     ) rt
where seqnum = 1;

Note: the where clause will probably not be able to take advantage of an index, so this requires a full table scan.

If you have an index on dialcode and you want to use it, then a more complicated query might be necessary. The following should have good performance:

select rt.*
from (select rt.*,
              row_number() over (partition by vendorid order by rate desc) as        
      from ((select rt.*
             from ratestable rt
             where dialcode = left('447400123456', 1)
            ) union all
            (select rt.*
             from ratestable rt
             where dialcode = left('447400123456', 2)
            )
            . . .
            (select rt.*
             from ratestable rt
             where dialcode = left('447400123456', 10)
            )
           ) rt
    ) rt
where seqnum = 1;

This seems more complicated, but each subquery optimally uses an index on dialcode.