SQL Server - Looping through records and updating value
Posted on 2014-04-14
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?