Urgent UPDATE statement 2

[DaveGreeko]

I really gave up on this problem and your help will really save my job. I have a DB with 2 tables to rate telephone calls. First Table stores the rate for each International destination and the second table stores the call records. Here is a real data from both tables:
tblRate(3 Fieleds):
DialCode,DestinationName,Rate
Sample Data:
49,Germany,0.02
491,Germany Mobile,0.18
4930,Germany Berlin,0.01

tblCall_Log(5 Fields)
CallId,CallDate,DialedNumber,Duration,Amount
Sample Data:
1,10/18/2006 10:09:45 AM,49148759654,3.5,NULL
2,10/18/2006 10:18:06 AM,49307896445,18.1,NULL
3,10/18/2006 10:30:31 AM,49835411472,2,NULL

All I need is an UPDATE statement to populate the Amount in tblCall_Log by looking up the rate in tblRate and multiply it by Duration of the call based on the prefix of the DialedNumber for example the first call (CallId=1) should be rated at 0.18 because its DialedNumber prefix is 491, Second call (called=2) should be rated at 0.01 because its DialedNumber prefix is 4930. As you can see the last call should be rated at 0.02

Thank you in advance for your help
Dave,

 

[bborissov]

Not tested at all:

UPDATE tblCall_Log
       SET Amount = COALESCE(Tbl1.rate*tblCall_Log.Duration,
                             Tbl2.rate*tblCall_Log.Duration,
                             Tbl3.rate*tblCall_Log.Duration)
FROM tblCall_Log
LEFT JOIN tblRate Tbl1 ON LEFT(tblCall_Log,4) = tbl1.DialCode,
LEFT JOIN tblRate Tbl2 ON LEFT(tblCall_Log,3) = tbl2.DialCode,
LEFT JOIN tblRate Tbl3 ON LEFT(tblCall_Log,2) = tbl3.DialCode

Make a goooooood backup first

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

Boris,

That is a really slick solution. I'll have to remember this one. One small glitch though. Still, nice work. Have a star.

UPDATE tblCall_Log
       SET Amount = COALESCE(Tbl1.rate*tblCall_Log.Duration,
                             Tbl2.rate*tblCall_Log.Duration,
                             Tbl3.rate*tblCall_Log.Duration)
FROM tblCall_Log
LEFT JOIN tblRate Tbl1 ON LEFT(tblCall_Log[!].DialedNumber[/!],4) = tbl1.DialCode,
LEFT JOIN tblRate Tbl2 ON LEFT(tblCall_Log[!].DialedNumber[/!],3) = tbl2.DialCode,
LEFT JOIN tblRate Tbl3 ON LEFT(tblCall_Log[!].DialedNumber[/!],2) = tbl3.DialCode

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DaveGreeko]

Thanks for your quick reply but this will not work for me since I have about 6000 International dialing codes. Germany was just a sample...
Thanks

 

[DaveGreeko]

Thanks Boris for your quick reply but this will not work for me since I have about 6000 International dialing codes. Germany was just a sample...
Thanks

 

[gmmastros]

Dave,

Before running the update statement, try selecting the data to see if it will return the correct results. Eventhough you have 6000 dialing codes, Boris's solution may still work for you.

[green]--UPDATE tblCall_Log
--       SET Amount = COALESCE(Tbl1.rate*tblCall_Log.Duration,
--                             Tbl2.rate*tblCall_Log.Duration,
--                             Tbl3.rate*tblCall_Log.Duration)[/green]
Select tblCall_Log.*, 
       Amount = COALESCE(Tbl1.rate*tblCall_Log.Duration,
                             Tbl2.rate*tblCall_Log.Duration,
                             Tbl3.rate*tblCall_Log.Duration)
FROM tblCall_Log
LEFT JOIN tblRate Tbl1 ON LEFT(tblCall_Log.DialedNumber,4) = tbl1.DialCode
LEFT JOIN tblRate Tbl2 ON LEFT(tblCall_Log.DialedNumber,3) = tbl2.DialCode
LEFT JOIN tblRate Tbl3 ON LEFT(tblCall_Log.DialedNumber,2) = tbl3.DialCode

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DaveGreeko]

Thanks George,
Before I test it, I could tell it is not going to work because Dialing code length varies among telecom carriers. What about the following scenario:
99528205, Geogria Mobile, 0.22
995, Georgia Land line, 0.06

As you can see the length of the Dialing code is 8 for the first entry. The problem with Boris’s solution is that you need the length of Dialing code in advance

 

[gmmastros]

Boris's code wouldn't accomodate a dialing code that is longer than 4 characters, but it would be simple enough to accomodate it.

I gave him a star (a special way of thanking people) because his solution is something I wouldn't have thought of.

Basically, he creates a join between the rate and call log table based on the first 4 characters of the phone number. Then another join on the first 3 characters, and then the first 2 characters. If the dialing code does not match the first 4 characters, then the left join will produce a null for the rate. Multiplying a number with NULL will result in NULL. That's OK though because he also used coalesce in the query which will take the first non-null value. To accomodate dialing codes up to 8 digits...

--UPDATE tblCall_Log
--       Amount = COALESCE(Tbl8.rate*tblCall_Log.Duration,
--                         Tbl7.rate*tblCall_Log.Duration,
--                         Tbl6.rate*tblCall_Log.Duration,
--                         Tbl5.rate*tblCall_Log.Duration,
--                         Tbl4.rate*tblCall_Log.Duration,
--                         Tbl3.rate*tblCall_Log.Duration,
--                         Tbl2.rate*tblCall_Log.Duration)
Select tblCall_Log.*, 
       Amount = COALESCE(Tbl8.rate*tblCall_Log.Duration,
                         Tbl7.rate*tblCall_Log.Duration,
                         Tbl6.rate*tblCall_Log.Duration,
                         Tbl5.rate*tblCall_Log.Duration,
                         Tbl4.rate*tblCall_Log.Duration,
                         Tbl3.rate*tblCall_Log.Duration,
                         Tbl2.rate*tblCall_Log.Duration)
FROM tblCall_Log
LEFT JOIN tblRate Tbl8 ON LEFT(tblCall_Log.DialedNumber,8) = tbl8.DialCode
LEFT JOIN tblRate Tbl7 ON LEFT(tblCall_Log.DialedNumber,7) = tbl7.DialCode
LEFT JOIN tblRate Tbl6 ON LEFT(tblCall_Log.DialedNumber,6) = tbl6.DialCode
LEFT JOIN tblRate Tbl5 ON LEFT(tblCall_Log.DialedNumber,5) = tbl5.DialCode
LEFT JOIN tblRate Tbl4 ON LEFT(tblCall_Log.DialedNumber,4) = tbl4.DialCode
LEFT JOIN tblRate Tbl3 ON LEFT(tblCall_Log.DialedNumber,3) = tbl3.DialCode
LEFT JOIN tblRate Tbl2 ON LEFT(tblCall_Log.DialedNumber,2) = tbl2.DialCode

You may have performance issues with this query if your call log table has millions of records, but give it a try to see if it works ok.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

Sorry I didn't answer, but I have a NET problems here. George thanks for the star. You describe the query beter than I could

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[DaveGreeko]

Boris, You are a genius
George, you tought me not to judge quickly..
I thank you so much guys; this really really saved my job…wow what a nice day…

 

[bborissov]

Hey it's a Friday, you must have a beer or two

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[DaveGreeko]

Thank you all again and have a good weekend