Do away with cursor

[djj55]

Hello, 2008R2
I am trying to get ride of a cursor and do not know where to start. What I am trying to do is assign items to agents.

For a give item type I have a set of agents. I have a number of accounts per item.
So say I have an item with 100 accounts to split between 3 agents. So first I try to even out the number of accounts 33, 33, 34.
To complicate any account that already has an agent assigned keeps that agent. I have this handled. I now have a table of how many each agent needs (#TotalsByAgnt). Now I want to go through myTable and assign agents to each account based on 1) ItemID and 2) number accounts an agent needs.

My cursor works fine, but I would like to find a way without a cursor.

Thanks.

[tt]
--#TotalsByAgnt (example)
AgentID ItemID RemCount
104 153 1
180 153 5
197 153 2
303 153 24
104 154 12
180 154 0
197 154 30
303 154 133

--myTable (example sub set)
Account ItemID
3384872 153
3384876 153
3384894 153
3384895 153
3384896 153
3377371 154
3377375 154
3377381 154
3377383 154
3377411 154[/tt]

DECLARE @RemCount INT
    , @AgentID INT 
    , @ItemID INT ; 

DECLARE AgentUpdate CURSOR FOR 
    SELECT AgentID, ItemID, RemCount
    FROM #TotalsByAgnt 
    WHERE NewCount IS NOT NULL; 

OPEN AgentUpdate; 
FETCH NEXT FROM AgentUpdate INTO @AgentID, @ItemID, @RemCount; 
-- Loop through the accounts assigning the agent and date
WHILE @@FETCH_STATUS = 0
BEGIN 
    IF @RemCount < 0 SET @RemCount = 0; 

    UPDATE ItemList 
    SET NewAgentID         = @AgentID
        ,NewAssignDate     = CAST(GETDATE() As DATE)
    FROM myTable ItemList 
    INNER JOIN (
            SELECT TOP (@RemCount) TQB.Account
            FROM myTable TQB 
            WHERE TQB.ItemID = @ItemID
                AND NewAgentID IS NULL 
            ORDER BY NEWID() ) X
        ON ItemList.Account = X.Account
    WHERE ItemList.ItemID = @ItemID; 

    FETCH NEXT FROM AgentUpdate INTO @AgentID, @ItemID, @RemCount; 
END --@@FETCH_STATUS = 0
CLOSE AgentUpdate; 
DEALLOCATE AgentUpdate;

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

 

[TheBugSlayer]

It should be something close to:

    UPDATE ItemList 
    SET NewAgentID         = @AgentID
        ,NewAssignDate     = CAST(GETDATE() As DATE)
    FROM myTable ItemList 
    INNER JOIN (
            SELECT TOP (@RemCount) TQB.Account
            FROM myTable TQB 
            WHERE TQB.ItemID = @ItemID
                AND NewAgentID IS NULL 
            ORDER BY NEWID() ) X ON ItemList.Account = X.Account
    INNER JOIN #TotalsByAgnt ON ItemList.ItemID = #TotalsByAgnt.ItemID;

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[djj55]

Thank you TheBugSlayer for the reply.

My cursor works fine as is, however, I would like to do this without a cursor.
I would like a way to assign a set of accounts from a group based on information in a table.

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

 

[TheBugSlayer]

You can avoid cursors with the proper use of a JOIN. I believe you are looking for something like the below:

;WITH cte AS
(
            SELECT TQB.*, row_number() OVER (PARTITION BY ItemID ORDER by ItemID) AS RowNumber
            FROM #myTtable TQB 
            --ORDER BY NEWID()
)
SELECT t.AgentID, t.ItemID, GETDATE() FROM cte
JOIN #TotalsByAgnt t ON t.ItemID = cte.ItemID
WHERE cte.RowNumber <= t.RemCount
ORDER BY t.AgentID

If you are not familiar with the syntax, read up on Common Table Expressions (CTE) in SQL Server Books Online (BOL).

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[djj55]

Very familiar. Been playing with row_number but was not going down the correct path. I will give your example a try tomorrow.
Thank you,

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