Help with removing cursor

[djj55]

Hello, SQL 2008R2
I have a table that holds an QueueID and the number of accounts per agent.
I have a second table that holds accounts and QueueID into which I want to put an AgentID.

To do this, the stored procedure I am editing currently uses a cursor to loop through the first table to update the second.

DECLARE C1 CURSOR FOR
    SELECT a.QueueID, a.AccountQty, qa.AgentID
    FROM #TempAllocations a
    INNER JOIN QueueAssignments qa 
	ON a.QueueID = qa.QueueID
    WHERE a.AccountQty > 0 
    ORDER BY a.QueueID 

OPEN C1
FETCH NEXT FROM C1 into @C1QueueDefnID, @C1AccountQty, @C1AgentID
--loop here ...
UPDATE T 
SET NewAgentID = @C1AgentID
FROM #TempQBuild T 
INNER JOIN (
        SELECT TOP (@C1AccountQty) RefNum 
        FROM #TempQBuild 
        WHERE NewQueueDefnID = @C1QueueDefnID
            AND NewAgentID = 91
        ORDER BY NEWID() ) X
    ON T.RefNum = X.RefNum
WHERE T.NewQueueDefnID = @C1QueueDefnID

How would I do this without the cursor? Or what sould I look up to help me?

Thank you,


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[SimonSellick]

Can you give a litle more information about what you are trying to achieve? Plus: what is in #TempQBuild and what is the significance of 91?

 

[djj55]

Thank you for the reply.

Sorry it was not clear.

I am trying to assign accounts to agents. This is done by table #TempAllocations joining to the QueueAssignments. This is used to assign agents a given number of accounts (AccountQty). The accounts for a given queue type (QueueID) are randomly assigned using the NEWID(). So I am currently looping through the cursor to assign every agent one at a time.

The table #TempAllocations having which queue and number of agents
The table #TempQBuild holds the accounts and what queue (QueueID) the account is in.
The 91 is for unassigned accounts (note this is a variable in the actual code).
Table QueueAssignments is which queue(s) the agent is in.

I hope this makes sense.
Thanks,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[SimonSellick]

Thanks for the extra info. Do you mean '.. which queue and number of ACCOUNTS'? If I've understood correctly, you essentially want a query that will return QueueID, AccountID, AgentID for all AccountID in #TempQBuild with NewAgentId = 91 and with the restriction that any AgentID must be used no more than #TempAllocations.AccountQty times for that AgentID and the associated QueueID. Can I assume that there are enough agents to go around? Actually, looking at your cursor, it appears that I can assume that there are exactly enough, so I guess that you have pre-calculated AccountQty to match the number of 91 entries? So the query should return exactly sum(#TempAllocations.AccountQty) rows. You can then use that in an update query. Let me know if that is correct (unless you are bored with waiting and have already sorted it out).

 

[djj55]

Yes a lot happens before this cursor like queue assignments and sum the number of accounts per queue.

What I hope to end up with is multiple accounts assigned to each agent. Each account will be marked with the AgentID. The AccountQty column is the number of accounts to assign to each agent. One agent may have multiple accounts from multiple queues but that is taken care of else where.

Only the unassigned accounts (91) are assigned an AgentID, and there may be accounts that do not get assigned.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[SimonSellick]

I've stalled. I feel that it should be possible to get what you need using PARTITION OVER and row_number() to get the correct number of rows for each agent, and your NEWID() idea to generate a random sort sequence, but I have not yet been able to come up with a query that actually does it - sorry. If you find an answer, please post it for others.

 

[djj55]

I will look into the partition over which I have not used much so forget about.

Thank you for the idea,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!