Randomly Assign to Group - 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

General SQL Server Forums

New to SQL Server Programming

Randomly Assign to Group

Author

Topic

rob26r
Starting Member

6 Posts

Posted - 2014-10-23 : 16:35:00

Hello - I have done some research and must not be using the right key words because I was not able to find anything.

My issue is that I have 10 accounts that were assigned to 5 agents with each agent receiving 2 accounts. I would know like to randomly reassign the accounts with the only criteria being that the random allocation not reassign to the same agency and each agent gets 2 accounts again.

Data looks like below and I want to populate the randomly assigned agent in the "Second_Agent" column.

Account, First_Agent, Second_Agent
B1, 1,
B2, 1,
B3, 2,
B4, 2,
B5, 3,
B6, 3,
B7, 4,
B8, 4,
B9, 5,
B10, 5,

Does anyone have any ideas/thoughts on how to most effectively achieve this using SQL? Thanks.

rob26r

AASC
Starting Member

24 Posts

Posted - 2014-10-24 : 01:11:11

@Rob26r here is the solution I came up with

---------------------------------------InPutTable
CREATE TABLE #Account
(
Account VARCHAR(20),
First_Agent INT,
Second_Agent INT,
)

---------------------------------------Sample Data
INSERT INTO #Account ( Account, First_Agent )
SELECT 'B1',1
UNION ALL
SELECT 'B2',1
UNION ALL
SELECT 'B3',2
UNION ALL
SELECT 'B4',2
UNION ALL
SELECT 'B5',3
UNION ALL
SELECT 'B6',3
UNION ALL
SELECT 'B7',4
UNION ALL
SELECT 'B8',4
UNION ALL
SELECT 'B9',5
UNION ALL
SELECT 'B10',5

---------------------------------------Temp Table for Distinct Agents
DECLARE @agents TABLE
(
ID INT IDENTITY(1, 1),
Agent_ID INT,
IsUsed BIT
)

INSERT INTO @agents ( Agent_ID )
SELECT DISTINCT First_Agent FROM #Account

---------------------------------------Loop for each agent assignment
DECLARE @looper INT, @CurrentAgent INT

SELECT @looper = ( SELECT MAX(id) FROM @agents )

WHILE ( @looper > 0 )
BEGIN

------------Select one agent at one time
SET @CurrentAgent = ( SELECT Agent_ID
FROM @agents
WHERE id = @looper
AND isused IS NULL
)

------------Update Second_Agent column with new agent
UPDATE acc
SET Second_Agent = @CurrentAgent
FROM ( SELECT Row_number() OVER ( ORDER BY First_Agent ) RID,
*
FROM #Account
WHERE First_Agent <> @CurrentAgent
AND Second_Agent IS NULL
) acc
WHERE acc.RID < 3

------------Update Bit of the agent already been assgined to some account
UPDATE @agents
SET IsUsed = 1
WHERE Agent_ID = @CurrentAgent

SELECT @looper = @looper - 1

END

SELECT * FROM #Account

DROP TABLE #Account

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-10-24 : 03:09:24

quote:
the only criteria being that the random allocation not reassign to the same agency and each agent gets 2 accounts again

What do you mean by "same agency" ?

KH
[spoiler]Time is always against us[/spoiler]

rob26r
Starting Member

6 Posts

Posted - 2014-10-24 : 08:44:45

AASC - thanks I will review your code and provide an update.

Khtan - sorry I said "same agency" but should have said the Second_Agent to get assigned the Account can't be the First_Agent. In other words, Account B1 was assigned to First_Agent 1 so when the account is reassigned it can't be assigned back to 1 because it was just placed there....

rob26r

rob26r
Starting Member

6 Posts

Posted - 2014-10-24 : 09:13:01

AASC - First, thanks. The code is doing what I asked for. Second, I definitely need to read up on a few of the concepts you used in your code.

I will spend some time trying to figure it out but is there a way to take your code and make a change so that if for example, the 10 accounts are getting reassigned to the Second_Agent except the Second_Agent is 1 and gets 2 out of the 10 accounts,Second_Agent 6 and gets 5 out of the 10 accounts,Second_Agent 7 and gets 3 out of the 10 accounts (Second_Agent 1 still can't be assigned Account B1 & B2 because they were placed as the First_Agent). The First_Agent 2,3,4,5 are not eligible to receive Accounts as a Second_Agent.

rob26r

AASC
Starting Member

24 Posts

Posted - 2014-10-24 : 17:37:18

What I got from your statement is
1. First_Agent 2,3,4,5 are not eligible to receive Accounts as Second_Agent
2. Remaining all First_agents are eligible to receive Accounts as Second_Agent
3. For each eligible Agent you want to define number of accounts to be assigned as Second_Agent
Is this what you are trying to do?

rob26r
Starting Member

6 Posts

Posted - 2014-10-25 : 10:30:49

@AASC - That is correct. Total eligible Second_Agent would be 1 (can't receive Accounts previously placed as First_Agent), 6(new), 7(new). Having the ability to define the number of accounts each agent gets would be incredible as business needs of course change over time.... THANKS!

rob26r

AASC
Starting Member

24 Posts

Posted - 2014-10-26 : 08:44:42

I Suppose there must be a table where you store
- Agent eligibility
- Number of accounts each agent can gets
are you? share its structure.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-10-26 : 11:54:06

[code]SET NOCOUNT ON;

DECLARE @Accounts TABLE
(
Account VARCHAR(20) NOT NULL,
First_Agent INT NOT NULL,
Second_Agent INT NULL
);

INSERT @Accounts
(
Account,
First_Agent
)
VALUES ('B1', 10),
('B2', 10),
('B3', 12),
('B4', 12),
('B5', 3),
('B6', 3),
('B7', 14),
('B8', 14),
('B9', 25),
('B10', 25);

-- SwePeso
WHILE EXISTS(SELECT cnt FROM (SELECT COUNT(*) AS cnt FROM @Accounts GROUP BY Second_Agent) AS d WHERE cnt <> 2)
UPDATE a
SET a.Second_Agent = f.Agent
FROM @Accounts AS a
CROSS APPLY (
SELECT TOP(1) w.First_Agent AS Agent
FROM @Accounts AS w
WHERE w.First_Agent <> a.First_Agent
ORDER BY NEWID()
) AS f(Agent);

-- Display result
SELECT *
FROM @Accounts;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

rob26r
Starting Member

6 Posts

Posted - 2014-10-27 : 18:53:46

@AASC - Since this approach to the issue is brand new I did not have a table created. This is currently done manually and takes many hours.

I envision the table looking like the following:

Table Headers:
AgentID,First_Agent_Eligible,First_Agent_Eligible_Percent_of_Accounts,Second_Agent_Eligible,Second_Agent_Eligible_Percent_of_Accounts

Data:
1,Y,0.2,Y,0.2
2,Y,0.2,N,0
3,Y,0.2,N,0
4,Y,0.2,N,0
5,Y,0.2,N,0
6,N,0,Y,0.5
7,N,0,Y,0.3
8,N,0,N,0
9,N,0,N,0
10,N,0,N,0

Anything you can help with would be appreciated...

Thanks!

rob26r

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources