Very complicated issue - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Very complicated issue

Author

Topic

oldfox
Starting Member

17 Posts

Posted - 2010-07-26 : 19:28:24

Hi all,
I need to verify cost allocation for a phone routing table.
Usually we use first 6 digits of a phone number, but it could be 7.
For example it may look like
310-929 =$0.01
310-929-2 =$0.02
So 310-929 has 9 breack outs (0-1 and 3-9)
We may get rates from carrier in any combination of breackout.
For example carrier might have rate only for 310-929, and we assign same rate to our list (310-929 and 310-929-2)

The issue to resolve is. Lets say carrier gives us rates like:
310-929 = $0.01
310-929-3 =$0.02
310-929-4 =0.005

We apply rates to our list and we can not change the list.
1. We need to catch that because carrier has rate for breackout 3 = $0.02 , we have to update cost of our 310-929 from $0.01 to $0.02
2. The cost for our 310-929-2 has to remain $0.02

Thanks

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-27 : 00:16:04

Whichever CTI you are using i'm 1000% sure you also have other CTI configuration columns (like channels, destination channels, queues, etc.) where you can set your query to get the right carrier just based on this rule. Determing also the direction of the call (inbound or outbound) you should also get this based on set of CTI configuration rules.
As for CallerID i use substring to resolve callerID because it always starts at the same starting position and you create your own set of rules for e.g.: 310-929 and 310-929-4 because by default the have different billings.

which telephony are you using?

parody
Posting Yak Master

111 Posts

Posted - 2010-07-27 : 05:52:59

Having trouble understanding this 100%

Why does 310-929-3 cause 310-929 to change from 0.01 to .02 when they dont match? And why would 310-929-2 be touched if the carrier doesnt provide a new rate for it anyway?

oldfox
Starting Member

17 Posts

Posted - 2010-07-27 : 12:32:48

Parody:
310-929 consists of all 10 breakouts, from 0 to 9.
In my first example in my list I do not have separate exception for 310-929-3, this breakout is part of 310-929.
Therefore if a carrier has rate for 310-929-3 = 0.02 and I keep the rate in my list for 929-310 = 0.01, all calls to 310-929-3 will be rated as 0.01 and i will lose money, because carrier charges me 0.02.
I can not expand my list for many reasons, so I have to keep my list of numbers intact.

Thanks for pointing to breakout 2 (310-929-2). Carrier does not have an exception for 310-929-2, therefore rate from carrier 310-929 will be applied and it has to be 0.01.

Slimt:
This is only cost allocation issue. Because carriers have different sets of breakouts, it is important to assign rates correctly.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-27 : 12:44:48

See my solution "4c" here http://ask.sqlservercentral.com/questions/2295/the-call-log-sql-problem

N 56°04'39.26"
E 12°55'05.63"

parody
Posting Yak Master

111 Posts

Posted - 2010-07-28 : 05:09:39

A relatively easy way to do this would be split it into 3 logical updates. It would work but is not necessarily the cleverest or most efficient way.

Update 1 - where breakout exists in both tables, set rates to be equal

Update 2 - where the breakout only exists in the internal table, set internal rate to the non-breakout rate from carrier table

Update 3 (must be last) - where the breakout only exists in the carrier table, set the internal non breakout rate to the maximum across all rates from the carrier table that do not appear in the internal table.

parody
Posting Yak Master

111 Posts

Posted - 2010-07-28 : 05:21:12

By the way updates 2 and 3 should of course always be also correlated by the base breakout (left 7), otherwise you will get rates from other breakouts (310-930,931 etc), assuming you have them all in one table.

parody
Posting Yak Master

111 Posts

Posted - 2010-07-28 : 07:34:19

Heres a CTE which provides a table to use as an update source. Assuming I've understood the requirements!

SELECT * FROM #InternalRates
SELECT * FROM #CarrierRates;

WITH UpdateRates AS
(
SELECT
-1 AS RowID
,Breakout
,(SELECT MAX(CR.Rate) FROM #CarrierRates CR WHERE NOT EXISTS (SELECT 1 FROM #InternalRates IR WHERE IR.Breakout = CR.Breakout /* include if max should inlcude the base breakout AND left(IR.Breakout,7) <> left(CR.Breakout,7)*/)) AS Rate
FROM #CarrierRates CR
WHERE LEN(Breakout) = 7
UNION ALL
SELECT
RowID + 1
,convert(varchar(9),left(Breakout,7) + '-' + CONVERT(char(1),RowID + 1))
,CASE
WHEN EXISTS (SELECT 1 FROM #InternalRates IR WHERE IR.Breakout = left(R.Breakout,7) + '-' + CONVERT(char(1),RowID + 1))
AND EXISTS (SELECT 1 FROM #CarrierRates CR WHERE CR.Breakout = left(R.Breakout,7) + '-' + CONVERT(char(1),RowID + 1))
THEN (SELECT Rate FROM #CarrierRates CR WHERE CR.Breakout = left(R.Breakout,7) + '-' + CONVERT(char(1),RowID + 1))
WHEN EXISTS (SELECT 1 FROM #InternalRates IR WHERE IR.Breakout = left(R.Breakout,7) + '-' + CONVERT(char(1),RowID + 1))
AND NOT EXISTS (SELECT 1 FROM #CarrierRates CR WHERE CR.Breakout = left(R.Breakout,7) + '-' + CONVERT(char(1),RowID + 1))
THEN (SELECT Rate FROM #CarrierRates CR WHERE CR.Breakout = left(R.Breakout,7))
ELSE NULL
END
FROM UpdateRates R
WHERE RowID < 9
)
SELECT
Breakout
,Rate
FROM UpdateRates
WHERE Rate IS NOT NULL

A further way would be a loop or cursor testing the scenario for each row, but depending on the size of this update that could be very ugly!

parody
Posting Yak Master

111 Posts

Posted - 2010-07-28 : 07:34:53

here are the source tables I was using

DROP TABLE #InternalRates
CREATE TABLE #InternalRates
(
Breakout varchar(9)
,Rate money
)

DROP TABLE #CarrierRates
CREATE TABLE #CarrierRates
(
Breakout varchar(9)
,Rate money
)

INSERT INTO #InternalRates
SELECT '310-929','0.01'
UNION SELECT '310-929-0','0.00'
UNION SELECT '310-929-1','0.01'
UNION SELECT '310-929-2','0.02'
UNION SELECT '310-929-3','0.03'
UNION SELECT '310-929-4','0.04'

INSERT INTO #CarrierRates
SELECT '310-929','0.01'
UNION SELECT '310-929-3','0.06'
UNION SELECT '310-929-4','0.07'
UNION SELECT '310-929-5','0.08'
UNION SELECT '310-929-6','0.09'

oldfox
Starting Member

17 Posts

Posted - 2010-09-29 : 19:52:16

Thank you Parody
It works for one destination like 310-929
But if we update a code and add second destination like 110-928 , it doesn't work for second destination.
Is there a way to adjust the query so it will work with thousands of destinations.

This is an update for the code you used in previous post
INSERT INTO #InternalRates
SELECT '110-928','0.15'
INSERT INTO #CarrierRates
SELECT '110-928-3','0.18'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources