There's gotta be a faster way to do this !!!

[DebbieChapman]

I work in telecoms company, and I'm looking into a legacy sytem to try and work out a more effiecient way of doing stuff. I've found one problem, that I cant see any other way to do. Wonder if anyone else has any ideas.

We keep a table of customers, and there telephone numbers (telephone numbers incl dialing code, eg 02476123456,the 02476 is the dialing code). We also keep a table of Dialing codes, eg 02476, 02479 etc etc)

each day we recieve a file of numbers called from that telephone number, and we rate the calls accordingly, unfortunaltely, do do this we need to seperate the dialing code from the number.

At the moment to do this we have a temp table that contains the telephone number, then 5 plus extra temp fields which are filled in by by Left([TelephoneNumber],4) Left ([telephoneNumber],5) etc etc, once this is done we then use several update querys to update a field for the DialingCode, by first matching the temp field with 6 digits to the dialing code table, then matching the temp field with 5 digits to the dialing code table etc etc.

This is very time consuming, especially when i can recive a file with 20,000 records in it.

I Would welcome any alternative suggestions

Thanks

 

[Steve101]

Assuming your two tables are called tblCode and tblCodeAndPhone respectively, and the tblCode table contains a nvarchar field called Code, and the tblCodeAndPhone table contains a similar field called CodeAndPhone, then the following SQL would allow you to identify the Code associated with eqch telephone number:

SELECT *
FROM tblCodeAndPhone CP
INNER JOIN tblCode C
ON C.Code = left(CP.CodeAndPhone,len(C.Code))

Given that this code can be derived for each number, I'm not sure why it might be necessary to update the table with this derivation. If it is required to explicitly update the table, then some additional SQL would need to be bolted on to the above, but ideally, you shouldnt need to hold this info as its already implicit in the relationship.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[r937]

steve, good one

the only problem is if the dialing code table has a 6-digit dialing code like 023456 and a 5-digit dialing code like 02345 then a phone number like 0234567879 will match them both

otherwise, yeah, i'd agree with everything else you said

rudy

http://r937.com/

SQL Consulting

 

[DebbieChapman]

Yes I'm afraid the dialing code table could contain the both the dialing codes 023456 and 02345, so it does match the two, any other ideas?

 

[r937]

pick the longest one first:

select max(len(left(pnumber,len(ccode)))
from ...

rudy

 

[Steve101]

sorry guys; i dont know much about telcos except that my wife and daughter use them to run up very large bills

There must be some sort of rule that I am missing. Does'nt the example as provided by rudy above imply an ambiguity (ie. either exchange code match); is the length of the telephone number implied by the exchange? If so, this has not yet been factored into the data structures. Clarification reqd pls,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[r937]

there is no ambiguity if you consider the original spec "by first matching the temp field with 6 digits to the dialing code table, then matching the temp field with 5 digits to the dialing code table etc etc."

the 6-digit code has precedence over the 5-digit code, which has precedence over the 4-digit code, etc.

presumably the objective is to select the first match based on longest code

hence my suggestion to use max(len(...


rudy

http://r937.com/

SQL Consulting

 

[Steve101]

ok, thanks Rudy, I understand. My question still holds though; does the exchange code determine the size of the phone number; ie. are all numbers fixed in size for each exchange. if so, this could be used to resolve any ambiguity.

or ... as rudy suggests, use of the max(len( ... approach, though the sql is a little more complex and eludes me at present

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Steve101]

k. got it. Try something like this:
[tt]
SELECT *, len(C.Code)
FROM tblCodeAndPhone CP
INNER JOIN tblCode C
ON C.Code = left(CP.CodeAndPhone,len(C.Code))
AND len(C.Code) = (SELECT MAX(LEN(C2.Code))
FROM tblCodeAndPhone CP2
INNER JOIN tblCode C2
ON C2.Code = left(CP.CodeAndPhone,len(C2.Code)))
[/tt]

Hope this does it.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[r937]

steve, looks great



rudy

http://r937.com/

SQL Consulting