June 5, 2014 at 7:50 am
Hello, I am randomly assigning agents to accounts. Currently I use a cursor, however I would like to use something else to populate the column.
CREATE TABLE Allocations (QueueID INT, PerAgent INT, AgentID INT);
INSERT INTO Allocations VALUES
(146,5,491),(146,5,463),(148,4,417),(148,4,393),(152,1,393),(152,1,472),(152,1,417),(154,1,463),(154,1,104);
CREATE TABLE MyBuild (RefNum INT, QueueID INT, AgentID INT);
INSERT INTO MyBuild (RefNum, QueueID) VALUES
(3760822, 154),(3760826, 154),(3760827, 154),(3760881, 154),(3760855, 152),(3769758, 152),(3771882, 146),
(3771889, 146),(3771893, 146),(3771910, 146),(3771912, 146),(3771926, 146),(3771929, 146),(3771933, 146),
(3771935, 146),(3771940, 146),(3772233, 148),(3772235, 148),(3772244, 148),(3772246, 148),(3772247, 148),
(3772248, 148),(3772249, 148),(3772250, 148)
/*
OUTPUT - the AgentID value is randomly assigned so my change RefNum value
RefNumQueueIDAgentID
3771882146491
3771889146491
3771893146463
3771910146491
3771912146463
3771926146463
3771929146491
3771933146463
3771935146463
3771940146491
3772233148417
3772235148417
3772244148393
3772246148393
3772247148393
3772248148417
3772249148393
3772250148417
3760855152393
3769758152472
3760822154417
3760826154104
3760827154463
3760881154NULL
*/
-- Current assignment
DECLARE @C1QueueID INT
,@C1AccountQty INT
,@C1AgentID INT;
DECLARE C1 CURSOR FOR
SELECT A.QueueID, A.PerAgent, A.AgentID
FROM Allocations A;
OPEN C1
FETCH NEXT FROM C1 INTO @C1QueueID, @C1AccountQty, @C1AgentID;
WHILE @@Fetch_Status= 0
BEGIN
UPDATE T
SET AgentID = @C1AgentID
FROM MyBuild T
INNER JOIN (
SELECT TOP (@C1AccountQty) TQB.RefNum
FROM MyBuild TQB
WHERE TQB.QueueID = @C1QueueID
ORDER BY NEWID() ) X
ON T.RefNum = X.RefNum
WHERE T.QueueID = @C1QueueID;
FETCH NEXT FROM C1 INTO @C1QueueID, @C1AccountQty, @C1AgentID;
END
CLOSE C1
DEALLOCATE C1
-- My try at a new way of populating AgentID (update statement to be added later)
-- This only gives me one of the QueueID values
SELECT TQB2.RefNum, TQB2.QueueID, TA.AgentID
FROM MyBuild TQB2
INNER JOIN Allocations TA ON TQB2.QueueID = TA.QueueID
INNER JOIN
(
SELECT TQB.RefNum, ROW_NUMBER() OVER(ORDER BY QueueID, NEWID()) AS RNum
FROM MyBuild TQB
) AS X ON TQB2.RefNum = X.RefNum
WHERE x.RNum <= TA.PerAgent
So where have I went wrong?
Thanks in advance
June 5, 2014 at 8:53 am
-- TESTING
-- Set up a CTE with agent numbers expanded out, randomly number the rows within each QueueID
;WITH RandomAgents AS (
SELECT QueueID, AgentID, rn = ROW_NUMBER() OVER(PARTITION BY QueueID ORDER BY NEWID())
FROM Allocations a
CROSS APPLY (SELECT TOP(a.PerAgent) n FROM (VALUES (1), (2), (3), (4), (5)) d (n)) t
)
SELECT *
FROM RandomAgents;
-- TESTING
-- Number the rows within each QueueID
;WITH NumberedBuild AS (
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY QueueID ORDER BY RefNum) FROM MyBuild
)
SELECT *
FROM NumberedBuild;
-- SOLUTION
-- Combine the two queries
;WITH RandomAgents AS (
SELECT QueueID, AgentID, rn = ROW_NUMBER() OVER(PARTITION BY QueueID ORDER BY NEWID())
FROM Allocations a
CROSS APPLY (SELECT TOP(a.PerAgent) n FROM (VALUES (1), (2), (3), (4), (5)) d (n)) t
), NumberedBuild AS (
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY QueueID ORDER BY RefNum) FROM MyBuild
)
SELECT b.*, '#' '#', r.*
FROM NumberedBuild b
LEFT JOIN RandomAgents r
ON b.QueueID = r.QueueID AND b.rn = r.rn
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 5, 2014 at 10:12 am
Thanks ChrisM@Work
Worked well on my sample data and I am now trying to get it to work on actual data.
I have replace the CROSS APPLY (SELECT TOP(a.PerAgent) n FROM (VALUES (1), (2), (3), (4), (5)) d (n)) t with
CROSS APPLY (SELECT TOP(a.PerAgent) Nval FROM NumberTable) t as the actual data may have a couple hundred for the PerAgent.
Now I need to find what I did to get back zeroes in the data. It should not be too hard though.
Thanks again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply