SQL Server - Looping through records and updating value

Posted on 2014-04-14
Last Modified: 2014-06-08
I have a table(Claims) in which I would like to take all records over a certain amount(PdAmt), assign them a random number to use for sorting and then go through the records alternating assigning them to users listed in another table.
So the process would be:
Select first claim record, assign first user in the table to this claim record
Select second claim record, assign second user in table to second claim record
and so on until all the claims have been assigned.

Also, if the process gets to the end of the user table and there are still more claims to be assigned it needs to start at the beginning of the user table again.

I have figured out how to assign the records a random number but I'm not sure what is the best/efficient method for alternating the assignment(update) between the users.

Basically my goal is to fairly assign high amount claims between all the users so one doesn't get a bunch of high amount claims.  

Can anyone point me in the right direction on this?
Question by:imstac73

Author Comment

ID: 40000424
ignore this
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40000440
I suggest using NTILE(nn), ORDERing by NEWID() (a random value).

But since the nn can't be a variable or joined column, the only way I've found so far is to use dynamic SQL.

Author Comment

ID: 40000551
Thanks Scott,
I've worked out the random value part; I need help with iterating through the tables to take turns assigning users to the claim records.

LVL 28

Expert Comment

by:Ryan McCauley
ID: 40094842
Do you want the users in the table to have the same number of high-value claims open at the same time? It's slightly different than what you're asking (assign evenly to everybody), but it would be easy to see who is next in line based on their number of open claims, and when they close one they become eligible for a new one. To do that, you'd need to find them using something like this:

-- Find next user eligible for large claim
-- If multiple users have the lowest number, it picks randomly using NEWID()
SELECT TOP 1 user, isnull(count(claims),0) as open_high_value_claims
  FROM users u
  JOIN claims c
    on u.userid = c.assignedtouserid
 WHERE c.value > 1000 -- or some other value
ORDER BY isnull(count(claims), 0), newid()

Open in new window

If you want to know who was assigned the last large claim so you can work your way down the user list, it's a bit more complicated - you'd have to take the following steps:

1. Determine the assigned person for the most recent large claim (if you're assigning them all as a batch in a single loop, you can skip this step as you'd just store their name in a variable between rounds)
2. Determine who is next in the list (increment ID?)
3. If you're at the end of the list, pick the first person instead

This still doesn't address the case where somebody isn't closing them and they're still getting assigned new claims - maybe it's not an issue in your case, but it would be simpler to do and would reward them for closing things out quickly and prevent any one agent from getting overloaded, if that's something you're interested in.

Accepted Solution

imstac73 earned 0 total points
ID: 40096940
Thanks for the comments. I ended up going with a different approach because I couldn't get it working and wasn't getting any posts and had a time deadline.  I just provided the admin user a datagrid of all high claims and allowed her to assign the claims manually.  There are no more than 20 in a given month.
LVL 28

Expert Comment

by:Ryan McCauley
ID: 40096992
Sounds good - sorry we weren't able to help you meet the deadline with an automated process, but at that volume it shouldn't be an issue to manage manually.
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud


Author Comment

ID: 40115848
Solution was not provided in adequate amount of time.

Author Comment

ID: 40116122
I've requested that this question be closed as follows:

Accepted answer: 0 points for imstac73's comment #a40115848

for the following reason:

No solution was provided by the time I needed it; used a different method
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40116123
Re-opening the question as the author has inadvertently accepted a non-solution (their own) and to allow them to accept a more appropriate comment as a solution or to request that the question be deleted.

Author Comment

ID: 40117299
There was no solution to my question.  The only solution that was posted was from Ryan but it did not address exactly what I needed and was not timely enough.  If there is an issue with closing the post then please delete it then.

LVL 28

Expert Comment

by:Ryan McCauley
ID: 40117350
Would my answer actually resolve the problem as stated? I don't care about the points, so if deleting is the right move I'm fine with that. That said, the purpose of closing a question rather than deleting it is to leave the discussion and posted comments available for future answer-seekers, and I'm hopeful that my proposed solution will help somebody in the future.

To the Admin's point, though, you did post the way you actually solved the problem, so I'd be fine if that was accepted. I think there are automated solutions beyond your final manual one, but if that's what works then it should be accepted.

Author Comment

ID: 40117554
I agree with you regarding closing the question instead of deleting; which is what I tried to do.

Even though your solution would not work for my situation ( I needed the claims all at once rather than only assigning one at a time. ); this may be helpful to someone else.

I will defer to the EE Moderator on what action I should take. :-)

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now