Round Robin Assignment implementation (database)

Question

I'm currently in the process of implementing a number of different assignment algorithms for a SalesLead <--> SalesPerson situation. The general idea is quite simple:

1. A SalesLead signs up on a public website

2. The code automatically assigns the SalesLead to a SalesPerson using round-robin scheduling (Leads are assigned to SalesPersons in the same order)

I'm having some serious coders-block in how to actually perform this outside of making sure I pull the agents from the database in the same order each time, and using another table to store the last agent that was assigned a SalesLead (which may actually be useful for building a report, or at least having a trace-back method later on). Am I way off base?

Clarification: The code doesn't currently match a sales person to a lead. This is a completely new function. Currently they can sign up for a specific SalesPerson, but we'd like them to be able to be assigned automatically.

Answer 1

If you have a table that matches SalesLeads and SalesPersons together and you timestamp it, you don't need a separate table to keep track of the last SalesPerson picked.

Answer 2

Pretty simple really: when a SalesPerson is created, give them a LastActivityDate. When they are assigned a SalesLead, update that date to the current date. Give a SalesLead, as it comes in, to a SalesPerson with the least recent activity date.

Can be done easily in SQL or code.

Answer 3

In SQL Server you'd actually want it to be a datetime, not a timestamp field, but @David's idea is the same. Keep track of the last sales lead assigned to a sales person and record the time at which it was assigned. You can then pick the next sales person to assign a lead to by finding the sales person who does not have one assigned or the sales person whose last assigned lead is the oldest.

Answer 4

Check out how Salesforce does it: http://forums.sforce.com/sforce/board/message?board.id=custom_formula&amp;message.id=533

MOD an ID by the number of salespeople + 1 and then assign 1 to Joe, 2, to Tom, etc.