• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

SQL Server - Looping through records and updating value

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?
1 Solution
imstac73Author Commented:
ignore this
Scott PletcherSenior DBACommented:
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.
imstac73Author Commented:
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.

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Ryan McCauleyData and Analytics ManagerCommented:
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.
imstac73Author Commented:
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.
Ryan McCauleyData and Analytics ManagerCommented:
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.
imstac73Author Commented:
Solution was not provided in adequate amount of time.
imstac73Author Commented:
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
Anthony PerkinsCommented:
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.
imstac73Author Commented:
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.

Ryan McCauleyData and Analytics ManagerCommented:
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.
imstac73Author Commented:
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. :-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now