Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


SQL Server - Looping through records and updating value

Posted on 2014-04-14
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Author Comment

ID: 40000424
ignore this
LVL 70

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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

618 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