分配组问题 [英] Problem with Assigning Group

问题描述

在SQL Server 2008R2中，我想编写T-SQL代码以根据面积将帐户分配给代理.

表一是一个帐户列表，其中包含按区域划分的GroupNum列，

In SQL Server 2008R2 I would like to write T-SQL code to assign account to agents based on area.

Table one is a list of accounts that has a column GroupNum by area,

Area - GROUPNUM - Account
A - 1 - 1
A - 1 - 2
A - 2 - 3
A - 2 - 4
A - 3 - 5
B - 4 - 6
B - 4 - 7
B - 5 - 8
(many more per Area/Group)
table two is a list of agent per area.
Area - Agent
A - John
A - Jane
A - Jack
B - Bill
B - Babs

我可以随机分配帐户(使用ORDER BY NEWID()命令)，但是问题是我希望所有的GROUPNUM都只分配给一个代理.因此，John将获得所有区域A的GRoupNUM 1，Jane将获得所有区域A的GroupNum2.

现在使问题复杂化，我想尝试均匀分配帐户.因此，如果有90个区域A帐户，则每个代理将获得30.但是，如果我为John帐户分配1，则他应获得帐户2.他现在拥有30个帐户中的两个.

这有意义吗?

我知道这应该很容易，但是我遇到了麻烦.

如果您能指出我的方向，我将不胜感激.
感谢

I can assign the accounts randomly (using the ORDER BY NEWID() command) but the problem is I want all of one GROUPNUM to go to only one agent. Thus John would get all Area A GRoupNUM 1 and Jane would get all Area A GroupNum 2.

Now to complicate the problem I want to try and evenly distribute the accounts. So if there are 90 Area A accounts the each agent would get 30. However if I assign John account 1 the he should get account 2. He now has two of the 30.

Does this make sense?

I know this should be easy but I am having trouble.

If you could point me in a direction I would be grateful.
Thanks

推荐答案

对于我要寻找的内容，我在分配给座席之前按区域/组对表进行了排序.然后，我分配了所需数量的帐户，之后检查最后分配的帐户是否具有相关帐户.这提供了我所需的信息，因为不会有大量的相关帐户，并且代理获得的少量额外帐户也没有关系.

For what I was looking for I sorted the table by Area/Group before assigning to an Agent. I then assigned the required number of accounts after which I check if the last assigned had related ones. This gives me what I need because there will not be a large number of related accounts, and the few extra the agent gets does not matter.

使用Row_Number()函数分配GroupNum给代理商.
请参见此处 [

Use Row_Number() function for having a GroupNum assigned to an agent.
See here[^]

这篇关于分配组问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！