I believe an SQL function is the best way to do what I need, but I'm not sure...
I need to distribute sales leads to remote offices. In a given zip code, there may be 0, 1, or more offices. If a lead comes in for a particular zip code, say 11111, and there is 1 office, I send it there. If there are zero, an administrator deals with it. If there is more than one, I want to distribute these round-robin. So, I have two tables:
Offices - Part of a crm system, and not updatable by us; the relevant columns are:
OfficePick - Our table that we can define as we choose:
What I want to happen is this logic:
If there is more than 1 office in a given zip code from offices
if any of these offices are not in OfficePick already, insert them with a distrib value of 0
select the one of the offices from the first 'if' that has the lowest distrib value
in the case of a tie, pick one randomly (could be rowid), doesn't really matter
increment the selected office's distrib value by 1
Any suggestions on the best way to implement this?