matching string from two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

matching string from two tables

Author

Topic

mixedupone3
Starting Member

8 Posts

Posted - 2002-02-27 : 16:35:40

Hello,
I am trying to match a dialed phone number in one table to its rate in a different table. It is easy if the rate table was all of one length.

number rate
011521 .02
011522 .01
011523 .01

I would use SUBSTRING(cdr.[dialed_number],1,6) = rate.[number]

But the rate table has numbers of variable length.

number rate
01152331 .05
0115233 .02
011523 .01
011524 .01
0115223 .02
0115221 .01
01152 .04

I need a way to match to the most significant number of digits. Ex: Match 0115233 at .02 when the dialed number is 01152334569855.

Any help would be very appreciated.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-27 : 16:57:16

Don't you want to allocate diferent rate cards to different accounts?

select rate.rate
from rate, cdr
where cdr.[dialed_number] like rate.[number] + '%'

to get the longest probably the most efficient way is to match on the longest and work your way down
Put the cdrs you want to rate into a temp table #rate(cdr, rate)
then
declare @i int
select @i = max(len(rate.[number]) from rate

while @i > 1
begin
update #rate
set rate = rate.rate
where left(#rate.[dialed_number],@i) = rate.[number]
and #rate.rate is null
end

I wrote something about writing an efficient telecom rating system some time ago - I'll try to dig it out.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

mixedupone3
Starting Member

8 Posts

Posted - 2002-02-28 : 12:26:52

Can't thank you enough. I have struggled with this all week. The following worked!! Took me a while to get the temp table working correctly. All I have to do now is incorporate this query into a perl script to produce reports. And yes I will have different rates for different accounts. I will have A different rate table per account, and Perl will create variables to be substituted into the query in place of the table name 'rate'.

declare @i int
select @i = max(len(rate.[number])) from rate
while @i > 1
begin
update #temprate
set #temprate.rate =
(select rate.rate from rate
where ((left(#temprate.[dialed_number],@i)) = rate.[number]))
where #temprate.rate is null
set @i = (@i - 1)
end

I would be interested in anything you have on an "efficient telecom rating system "

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources