Assigning Records to Users and Keeping Their Workload Balanced

[Guest]

I am very new to this and need some help..

I have set-up a table for assigning, scheduling, and tracking patient accounts for insurance follow-up. The source for this data comes from an external system. Every night, the external system produces a file of new patient encounters that we upload into the database. Each patient has a unique account number and each account number may have one or more encounter dates. In the table the records appear as follows

Patient Number, Patient Name, Encounter Date, User Assigned, etc. etc

100005433, John Smith, 02/05/2004, USER
100005433, John Smith, 02/12/2004, USER
100005433, John Smith, 02/28/2004, USER
100008702, Fred Jones, 02/07/2004, USER
100008702, Fred Jones, 02/10/2004, USER

What I need to do is when a new record is pulled into the database, I have to find a way to assign the record to one of a group of six users. The catch is that this cannot be at random. When a record comes in for a new patient encounter but that patient's account number already exists in our database, then the new record must be assigned to the same user that already has the other encounters for that same account number. If the new record is for an account number that we do not already have in the database, then we have to assign to any of the users. But... we don't want to just deal these new account numbers out at random. We want to be able to assign new records to the users who have the fewest records already assigned to them. The plan being to try to keep all six users with about the same number of records to work. Worse yet, existing records do finally get worked to completion and they become flagged as "inactive" in a record status field. The "inactive" records are not to be counted when trying to balance the workload

SO... is this something that can be done realistically? I imagine that setting this up will be rather complicated. Any suggestions on how to accomplish this task

Thanks
Davi

 

[Rebecca Riordan]

David,

I think life will be easier if you load the records into a temporary table
first. Then you can use a series of queries to perform your actions.
You'll probably need to combine them in code--maybe the procedure that loads
the data?

1) Update the UserAssigned where there's a match
2) Count the remaining new encounters (UserAssigned = Null, or however you
get them)
3) Count the number of existing records for each user where status <>
"inactive"
4) Assign the new encounters proportionally.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

I am very new to this and need some help...

I have set-up a table for assigning, scheduling, and tracking patient

accounts for insurance follow-up. The source for this data comes from an
external system. Every night, the external system produces a file of new
patient encounters that we upload into the database. Each patient has a
unique account number and each account number may have one or more encounter
dates. In the table the records appear as follows:

Patient Number, Patient Name, Encounter Date, User Assigned, etc. etc.

100005433, John Smith, 02/05/2004, USER 1
100005433, John Smith, 02/12/2004, USER 1
100005433, John Smith, 02/28/2004, USER 1
100008702, Fred Jones, 02/07/2004, USER 2
100008702, Fred Jones, 02/10/2004, USER 2

What I need to do is when a new record is pulled into the database, I have

to find a way to assign the record to one of a group of six users. The
catch is that this cannot be at random. When a record comes in for a new
patient encounter but that patient's account number already exists in our
database, then the new record must be assigned to the same user that already
has the other encounters for that same account number. If the new record is
for an account number that we do not already have in the database, then we
have to assign to any of the users. But... we don't want to just deal these
new account numbers out at random. We want to be able to assign new records
to the users who have the fewest records already assigned to them. The plan
being to try to keep all six users with about the same number of records to
work. Worse yet, existing records do finally get worked to completion and
they become flagged as "inactive" in a record status field. The "inactive"
records are not to be counted when trying to balance the workload.

SO... is this something that can be done realistically? I imagine that

setting this up will be rather complicated. Any suggestions on how to
accomplish this task?

 

[Guest]

Thanks Rebecca. I made the changes to where I am now importing the records into a separate table. Then, I run a query that finds account numbers that were already assigned to a user in the original table and make that same assignment in the new temporary table. Then, I run an append query to move those records into my original table. At this point, I still have the remaining unassigned records in the temporary table that need to be assigned and then appended to the original table

Questions at this point..

Any suggestions on how to go about determining the number of accounts remaining that should be assigned to each user? To make it easier for illustration, say I have 4 users that have the following volume of current active account assignments

User 1 --- 300 account
User 2 --- 400 account
User 3 --- 500 account
User 4 --- 600 account

Let's suppose that I have 700 accounts in the temporary file that have not yet been assigned. What we want to have occur would be as follows

User 1 gets 325 accounts bringing User 1's new total to 625 accounts
User 2 gets 225 accounts bringing User 2's new total to 625 accounts
User 3 gets 125 accounts bringing User 3's new total to 625 accounts
User 4 gets 25 accounts brining User 4's new total to 625 accounts

All 700 accounts have been assigned and each user now has the same volume of accounts to work

An additional consideration is that in the temporary table there may be duplicate account numbers. When that occurs, each of the duplicated account numbers (for different service dates) need to be assigned to one user and not split up between users

Any ideas on how to make this part happen? I am about halfway to solving my problem at this point

Thanks
David

 

[Rebecca Riordan]

Sounds like you're going well. Now...algorithm design isn't really my best
trick, but let's see what we can come up with.

How about this...you do a no-duplicates count of records in both tables,
which you can get with totals queries. Using your example, you'd have 1800
in the primary table, + 700 in the new table = 2500. Divide that by the
number of users (use another total query --you _don't_ want to be fiddling
with this once you get it working) so 2500 / 4 = 625. Then you add (625 -
current number for that user) to each account. You'd need to do some
trapping for the numbers not coming out even -- use integer math, and maybe
assign any leftover accounts to a user chosen at random?

Does that help, or do you need to code sample? (Please say no...I have to
do my taxes today <eg>)

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

Thanks Rebecca. I made the changes to where I am now importing the

records into a separate table. Then, I run a query that finds account
numbers that were already assigned to a user in the original table and make
that same assignment in the new temporary table. Then, I run an append
query to move those records into my original table. At this point, I still
have the remaining unassigned records in the temporary table that need to be
assigned and then appended to the original table.

Questions at this point...

Any suggestions on how to go about determining the number of accounts

remaining that should be assigned to each user? To make it easier for
illustration, say I have 4 users that have the following volume of current
active account assignments:

User 1 --- 300 accounts
User 2 --- 400 accounts
User 3 --- 500 accounts
User 4 --- 600 accounts

Let's suppose that I have 700 accounts in the temporary file that have not

yet been assigned. What we want to have occur would be as follows:

User 1 gets 325 accounts bringing User 1's new total to 625 accounts.
User 2 gets 225 accounts bringing User 2's new total to 625 accounts.
User 3 gets 125 accounts bringing User 3's new total to 625 accounts.
User 4 gets 25 accounts brining User 4's new total to 625 accounts.

All 700 accounts have been assigned and each user now has the same volume of accounts to work.

An additional consideration is that in the temporary table there may be

duplicate account numbers. When that occurs, each of the duplicated account
numbers (for different service dates) need to be assigned to one user and
not split up between users.

 

[Guest]

Rebecca

I get the idea of where you are going with this but I sure don't know how to code it. If you're willing to provide it, a code sample would be great. No hurry though. I have some lead time before I have to come up with a way to solve the account assignment problem

Thanks
David

 

[Rebecca Riordan]

David,

Take "NOSPAM" out of my reply address, zip up a SMALL version of your
database (delete most of the data, all the forms, and only the tables and
queries that we're working with), and send me a copy. I'll get a sample put
together (working, but no error-handling) and send it back to you within the
next week or so.

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

Rebecca,

I get the idea of where you are going with this but I sure don't know how

to code it. If you're willing to provide it, a code sample would be great.
No hurry though. I have some lead time before I have to come up with a way
to solve the account assignment problem.