Problem with Assigning Group

See more:

SQL-Server

T-SQL

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

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