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

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.
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!


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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

746 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

11 Experts available now in Live!

Get 1:1 Help Now