February 29, 2012 at 3:37 pm
I want to design the tables and a stored proc that will facilitate the retrieval of a phone number to an application. Basically, the client can specify that they want 10% of the calls to go to site A, 20% to go to site B, and 70% of the calls to go to site C between 7am and noon, as an example. So, the stored procedure and tables need to be set up to return the proper phone number for the application to dial based on the time and the percentage. The main table for this will hold the time range, the lookup information for the phone number, and the % of requests that should return that number during that time range. Here is some sample DDL. NOTE: Table structures are NOT complete and may be changed. This is still in R&D phase:
USE test
--Create a reference table for 3 different sites
CREATE TABLE sites
(siteId INT IDENTITY,
siteName VARCHAR(25))
--Insert 3 sites
INSERT INTO sites
SELECT 'ILLINOIS'
UNION ALL
SELECT 'TEXAS'
UNION ALL
SELECT 'WASHINGTON'
--Create reference table for 2 different applications
CREATE TABLE apps
(appId INT,
appName VARCHAR(25))
--Insert 2 applications
INSERT INTO apps
SELECT 1234, 'APPLICATION1'
UNION ALL
SELECT 5678, 'APPLICATION2'
--Create reference table for phone numbers to a specific site/app
CREATE TABLE sitePhoneXref
(id INT IDENTITY,
siteId INT,
appId INT,
phone VARCHAR(10))
--Insert 3 sites each with 2 apps
INSERT INTO sitePhoneXref
SELECT 1, 1234, '1234567890'
UNION ALL
SELECT 1, 5678, '9876543210'
UNION ALL
SELECT 2, 1234, '5463121456'
UNION ALL
SELECT 2, 5678, '3541236549'
UNION ALL
SELECT 3, 1234, '7896512345'
UNION ALL
SELECT 3, 5678, '6572234856'
--Create table for saying which percentage of calls
-- for a specific app should go to a specific site
-- during a specified time range
CREATE TABLE timeRouting
(id INT IDENTITY,
dayOfWk INT,
timeStart TIME,
timeEnd TIME,
siteId INT,
appId INT,
pctTransferred INT)
--Insert data for 7 days, 2 apps, 3 sites, and 3 time ranges for each day (7*2*3*3 rows)
INSERT INTO timeRouting
SELECT 1, '00:00', '13:15', 1, 1234, 10
UNION ALL
SELECT 1, '00:00', '13:15', 2, 1234, 40
UNION ALL
SELECT 1, '00:00', '13:15', 3, 1234, 50
UNION ALL
SELECT 1, '13:15', '18:00', 1, 1234, 20
UNION ALL
SELECT 1, '13:15', '18:00', 2, 1234, 60
UNION ALL
SELECT 1, '13:15', '18:00', 3, 1234, 20
UNION ALL
SELECT 1, '18:00', '00:00', 1, 1234, 30
UNION ALL
SELECT 1, '18:00', '00:00', 2, 1234, 40
UNION ALL
SELECT 1, '18:00', '00:00', 3, 1234, 30
UNION ALL
SELECT 2, '00:00', '13:15', 1, 1234, 10
UNION ALL
SELECT 2, '00:00', '13:15', 2, 1234, 40
UNION ALL
SELECT 2, '00:00', '13:15', 3, 1234, 50
UNION ALL
SELECT 2, '13:15', '18:00', 1, 1234, 20
UNION ALL
SELECT 2, '13:15', '18:00', 2, 1234, 60
UNION ALL
SELECT 2, '13:15', '18:00', 3, 1234, 20
UNION ALL
SELECT 2, '18:00', '00:00', 1, 1234, 30
UNION ALL
SELECT 2, '18:00', '00:00', 2, 1234, 40
UNION ALL
SELECT 2, '18:00', '00:00', 3, 1234, 30
UNION ALL
SELECT 3, '00:00', '13:15', 1, 1234, 10
UNION ALL
SELECT 3, '00:00', '13:15', 2, 1234, 40
UNION ALL
SELECT 3, '00:00', '13:15', 3, 1234, 50
UNION ALL
SELECT 3, '13:15', '18:00', 1, 1234, 20
UNION ALL
SELECT 3, '13:15', '18:00', 2, 1234, 60
UNION ALL
SELECT 3, '13:15', '18:00', 3, 1234, 20
UNION ALL
SELECT 3, '18:00', '00:00', 1, 1234, 30
UNION ALL
SELECT 3, '18:00', '00:00', 2, 1234, 40
UNION ALL
SELECT 3, '18:00', '00:00', 3, 1234, 30
UNION ALL
SELECT 4, '00:00', '13:15', 1, 1234, 10
UNION ALL
SELECT 4, '00:00', '13:15', 2, 1234, 40
UNION ALL
SELECT 4, '00:00', '13:15', 3, 1234, 50
UNION ALL
SELECT 4, '13:15', '18:00', 1, 1234, 20
UNION ALL
SELECT 4, '13:15', '18:00', 2, 1234, 60
UNION ALL
SELECT 4, '13:15', '18:00', 3, 1234, 20
UNION ALL
SELECT 4, '18:00', '00:00', 1, 1234, 30
UNION ALL
SELECT 4, '18:00', '00:00', 2, 1234, 40
UNION ALL
SELECT 4, '18:00', '00:00', 3, 1234, 30
UNION ALL
SELECT 5, '00:00', '13:15', 1, 1234, 10
UNION ALL
SELECT 5, '00:00', '13:15', 2, 1234, 40
UNION ALL
SELECT 5, '00:00', '13:15', 3, 1234, 50
UNION ALL
SELECT 5, '13:15', '18:00', 1, 1234, 20
UNION ALL
SELECT 5, '13:15', '18:00', 2, 1234, 60
UNION ALL
SELECT 5, '13:15', '18:00', 3, 1234, 20
UNION ALL
SELECT 5, '18:00', '00:00', 1, 1234, 30
UNION ALL
SELECT 5, '18:00', '00:00', 2, 1234, 40
UNION ALL
SELECT 5, '18:00', '00:00', 3, 1234, 30
UNION ALL
SELECT 6, '00:00', '13:15', 1, 1234, 10
UNION ALL
SELECT 6, '00:00', '13:15', 2, 1234, 40
UNION ALL
SELECT 6, '00:00', '13:15', 3, 1234, 50
UNION ALL
SELECT 6, '13:15', '18:00', 1, 1234, 20
UNION ALL
SELECT 6, '13:15', '18:00', 2, 1234, 60
UNION ALL
SELECT 6, '13:15', '18:00', 3, 1234, 20
UNION ALL
SELECT 6, '18:00', '00:00', 1, 1234, 30
UNION ALL
SELECT 6, '18:00', '00:00', 2, 1234, 40
UNION ALL
SELECT 6, '18:00', '00:00', 3, 1234, 30
UNION ALL
SELECT 7, '00:00', '13:15', 1, 1234, 10
UNION ALL
SELECT 7, '00:00', '13:15', 2, 1234, 40
UNION ALL
SELECT 7, '00:00', '13:15', 3, 1234, 50
UNION ALL
SELECT 7, '13:15', '18:00', 1, 1234, 20
UNION ALL
SELECT 7, '13:15', '18:00', 2, 1234, 60
UNION ALL
SELECT 7, '13:15', '18:00', 3, 1234, 20
UNION ALL
SELECT 7, '18:00', '00:00', 1, 1234, 30
UNION ALL
SELECT 7, '18:00', '00:00', 2, 1234, 40
UNION ALL
SELECT 7, '18:00', '00:00', 3, 1234, 30
UNION ALL
SELECT 1, '00:00', '13:15', 1, 5678, 10
UNION ALL
SELECT 1, '00:00', '13:15', 2, 5678, 40
UNION ALL
SELECT 1, '00:00', '13:15', 3, 5678, 50
UNION ALL
SELECT 1, '13:15', '18:00', 1, 5678, 20
UNION ALL
SELECT 1, '13:15', '18:00', 2, 5678, 60
UNION ALL
SELECT 1, '13:15', '18:00', 3, 5678, 20
UNION ALL
SELECT 1, '18:00', '00:00', 1, 5678, 30
UNION ALL
SELECT 1, '18:00', '00:00', 2, 5678, 40
UNION ALL
SELECT 1, '18:00', '00:00', 3, 5678, 30
UNION ALL
SELECT 2, '00:00', '13:15', 1, 5678, 10
UNION ALL
SELECT 2, '00:00', '13:15', 2, 5678, 40
UNION ALL
SELECT 2, '00:00', '13:15', 3, 5678, 50
UNION ALL
SELECT 2, '13:15', '18:00', 1, 5678, 20
UNION ALL
SELECT 2, '13:15', '18:00', 2, 5678, 60
UNION ALL
SELECT 2, '13:15', '18:00', 3, 5678, 20
UNION ALL
SELECT 2, '18:00', '00:00', 1, 5678, 30
UNION ALL
SELECT 2, '18:00', '00:00', 2, 5678, 40
UNION ALL
SELECT 2, '18:00', '00:00', 3, 5678, 30
UNION ALL
SELECT 3, '00:00', '13:15', 1, 5678, 10
UNION ALL
SELECT 3, '00:00', '13:15', 2, 5678, 40
UNION ALL
SELECT 3, '00:00', '13:15', 3, 5678, 50
UNION ALL
SELECT 3, '13:15', '18:00', 1, 5678, 20
UNION ALL
SELECT 3, '13:15', '18:00', 2, 5678, 60
UNION ALL
SELECT 3, '13:15', '18:00', 3, 5678, 20
UNION ALL
SELECT 3, '18:00', '00:00', 1, 5678, 30
UNION ALL
SELECT 3, '18:00', '00:00', 2, 5678, 40
UNION ALL
SELECT 3, '18:00', '00:00', 3, 5678, 30
UNION ALL
SELECT 4, '00:00', '13:15', 1, 5678, 10
UNION ALL
SELECT 4, '00:00', '13:15', 2, 5678, 40
UNION ALL
SELECT 4, '00:00', '13:15', 3, 5678, 50
UNION ALL
SELECT 4, '13:15', '18:00', 1, 5678, 20
UNION ALL
SELECT 4, '13:15', '18:00', 2, 5678, 60
UNION ALL
SELECT 4, '13:15', '18:00', 3, 5678, 20
UNION ALL
SELECT 4, '18:00', '00:00', 1, 5678, 30
UNION ALL
SELECT 4, '18:00', '00:00', 2, 5678, 40
UNION ALL
SELECT 4, '18:00', '00:00', 3, 5678, 30
UNION ALL
SELECT 5, '00:00', '13:15', 1, 5678, 10
UNION ALL
SELECT 5, '00:00', '13:15', 2, 5678, 40
UNION ALL
SELECT 5, '00:00', '13:15', 3, 5678, 50
UNION ALL
SELECT 5, '13:15', '18:00', 1, 5678, 20
UNION ALL
SELECT 5, '13:15', '18:00', 2, 5678, 60
UNION ALL
SELECT 5, '13:15', '18:00', 3, 5678, 20
UNION ALL
SELECT 5, '18:00', '00:00', 1, 5678, 30
UNION ALL
SELECT 5, '18:00', '00:00', 2, 5678, 40
UNION ALL
SELECT 5, '18:00', '00:00', 3, 5678, 30
UNION ALL
SELECT 6, '00:00', '13:15', 1, 5678, 10
UNION ALL
SELECT 6, '00:00', '13:15', 2, 5678, 40
UNION ALL
SELECT 6, '00:00', '13:15', 3, 5678, 50
UNION ALL
SELECT 6, '13:15', '18:00', 1, 5678, 20
UNION ALL
SELECT 6, '13:15', '18:00', 2, 5678, 60
UNION ALL
SELECT 6, '13:15', '18:00', 3, 5678, 20
UNION ALL
SELECT 6, '18:00', '00:00', 1, 5678, 30
UNION ALL
SELECT 6, '18:00', '00:00', 2, 5678, 40
UNION ALL
SELECT 6, '18:00', '00:00', 3, 5678, 30
UNION ALL
SELECT 7, '00:00', '13:15', 1, 5678, 10
UNION ALL
SELECT 7, '00:00', '13:15', 2, 5678, 40
UNION ALL
SELECT 7, '00:00', '13:15', 3, 5678, 50
UNION ALL
SELECT 7, '13:15', '18:00', 1, 5678, 20
UNION ALL
SELECT 7, '13:15', '18:00', 2, 5678, 60
UNION ALL
SELECT 7, '13:15', '18:00', 3, 5678, 20
UNION ALL
SELECT 7, '18:00', '00:00', 1, 5678, 30
UNION ALL
SELECT 7, '18:00', '00:00', 2, 5678, 40
UNION ALL
SELECT 7, '18:00', '00:00', 3, 5678, 30
--Shows that between midnight and 1:15pm, for application 1234
-- 10% of requests should return the phone number for site/app 1/1234
-- 40% of requests should return the phone number for site/app 2/1234
-- 50% of requests should return the phone number for site/app 3/1234
SELECT *
FROM timeRouting
WHERE dayOfWk = 1 AND timeStart = '00:00' AND appId = 1234
So one thought was to have the stored proc randomly generate a number between 1 and 100 and use that number to retrieve a specific phone number (if the randomly generated number is between 1 and 10, then return the number for site 1, app 1234. Between 11 and 50 then return the number for site 2, app 1234...). The problem with this approach is that it requires many requests during the time range to approach the required %age for a site.
Any thoughts?
Jared
CE - Microsoft
February 29, 2012 at 4:18 pm
Can you provide us a sample input and expected output for that sample input?
February 29, 2012 at 4:20 pm
I would use an auxiliary table to hold the call information:
CREATE TABLE calls
(
callid INT IDENTITY(1,1),
appId INT,
callTime DATETIME,
siteId INT NULL
)
Each call would be inserted before a site has been assigned (with NULL value for siteId).
Then I would calculate the current percentage per site and app in the time range of the unassigned call. The max. CurrentPctTransferred value that is lower than the (Target) PctTransferred value would be assigned to this call.
For the given scenario this would lead to
PctTransferred
Target104050
CallSite123
call1300100
call2205050
call31333333(might be assigned to site 3 depending on equal or less, influencing all future calls in that range)
call43252550
call52204040
call63173350
call72144343
call83133750
March 1, 2012 at 8:24 am
ColdCoffee (2/29/2012)
Can you provide us a sample input and expected output for that sample input?
I am waiting on the sample input, but I am pretty sure that it will be something like this:
EXEC nameOfStoredProcedure @callDateAndTime = '2012-03-01 07:02:05.000', @appId = 1234
The sample output will be exactly the result of this query:
SELECT phone FROM dbo.sitePhoneXref WHERE phone = '1234567890'
Jared
CE - Microsoft
March 1, 2012 at 8:30 am
LutzM (2/29/2012)
I would use an auxiliary table to hold the call information:
CREATE TABLE calls(
callid INT IDENTITY(1,1),
appId INT,
callTime DATETIME,
siteId INT NULL
)
Each call would be inserted before a site has been assigned (with NULL value for siteId).
Then I would calculate the current percentage per site and app in the time range of the unassigned call. The max. CurrentPctTransferred value that is lower than the (Target) PctTransferred value would be assigned to this call.
For the given scenario this would lead to
PctTransferred
Target104050
CallSite123
call1300100
call2205050
call31333333(might be assigned to site 3 depending on equal or less, influencing all future calls in that range)
call43252550
call52204040
call63173350
call72144343
call83133750
I was thinking something in the same fashion. However, maybe calculate an order first? i.e. If we know for a given time that 10% of calls go to siteA, 80 to siteB, and 10 to siteC... Then look at the count of calls in the time frame and return a value accordingly. So first 8 calls go to B, next one goes to A, and next goes to C. Then repeat the process. It is "Ok" is we only receive 8 calls in the time frame that they all go to B because this is an unlikely scenario.
Which solution (calculating percentage upon each call to the sp versus calculating a set order of calls before the sp is called and determining which number is next to be returned) do you all think is best?
Jared
CE - Microsoft
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply