Function to implement 'round-robin' selection?

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:
Office_ID char(5)
Zip char(5)

OfficePick - Our table that we can define as we choose:
Office_ID char(5)
Distrib int

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?
Chuck BrownAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ErnariashCommented:

Is Office_ID unique? Then this could work for you?

---- if any of these offices are not in OfficePick already, insert them with a distrib value of 0
INSERT INTO OfficePick
SELECT Office_ID, 0 
FROM Offices
WHERE NOT EXISTS (
		SELECT 1
		FROM OfficePick 
		WHERE  Offices.Office_ID = OfficePick.Office_ID 
)
 
--If there is more than 1 office in a given zip code from offices ONE OFFICE ID PER ZIP with the less Distrib
 
SELECT Zip, MAX(Office_ID) Office_ID
INTO #YOURPIK
FROM Offices
WHERE EXISTS   (
		SELECT 1 FROM (
				SELECT  Zip, MIN(Distrib) MINDistrib
				FROM Offices 
				INNER JOIN  OfficePick 
				ON  Offices.Office_ID = OfficePick.Office_ID
		) T
		WHERE  Offices.Office_ID = T.Office_ID
 
	)
GROUP BY Zip
	 
---update the picked office with Distrib+1	 
UPDATE T
SET Distrib = Distrib+1
FROM OfficePick T
INNER JOIN #YOURPIK
ON  #YOURPIK.Office_ID = T.Office_ID

Open in new window

ErnariashCommented:
Correction on the:

SELECT Zip, MAX(Offices.Office_ID ) Office_ID
INTO #YOURPIK
FROM Offices
INNER JOIN OfficePick
ON Offices.Office_ID = OfficePick.Office_ID
WHERE EXISTS (
SELECT 1 FROM (
SELECT Zip, Offices.Office_ID, MIN(Distrib) MINDistrib
FROM Offices
INNER JOIN OfficePick
ON Offices.Office_ID = OfficePick.Office_ID
GROUP BY Zip, Offices.Office_ID
) T
WHERE Offices.Zip = T.Zip AND Offices.Office_ID = OfficePick.Office_ID AND MINDistrib = OfficePick.Distrib

)
GROUP BY Zip

---- if any of these offices are not in OfficePick already, insert them with a distrib value of 0
INSERT INTO OfficePick
SELECT Office_ID, 0 
FROM Offices
WHERE NOT EXISTS (
		SELECT 1
		FROM OfficePick 
		WHERE  Offices.Office_ID = OfficePick.Office_ID 
)
 
--If there is more than 1 office in a given zip code from offices ONE OFFICE ID PER ZIP with the less Distrib
 
SELECT Zip, MAX(Offices.Office_ID ) Office_ID
INTO #YOURPIK
FROM Offices
INNER JOIN  OfficePick 
	ON  Offices.Office_ID = OfficePick.Office_ID
WHERE EXISTS   (
		SELECT 1 FROM (
				SELECT  Zip, Offices.Office_ID, MIN(Distrib) MINDistrib
				FROM Offices 
				INNER JOIN  OfficePick 
				ON  Offices.Office_ID = OfficePick.Office_ID
				GROUP BY Zip, Offices.Office_ID 
		) T
		WHERE  Offices.Zip = T.Zip AND Offices.Office_ID = OfficePick.Office_ID AND MINDistrib = OfficePick.Distrib
 
	)
GROUP BY Zip
	 
---update the picked office with Distrib+1	 
UPDATE T
SET Distrib = Distrib+1
FROM OfficePick T
INNER JOIN #YOURPIK
ON  #YOURPIK.Office_ID = T.Office_ID

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chuck BrownAuthor Commented:
Ok, I've done some testing, and gotten some further clarification... I think this is close!  However, as it turns out, the CRM system will be simply updating our table on a daily basis.  So we've gone to only one table that will have all of our required information in it, and we only need to do the selection and the distrib updating.  It looks like this:

OfficePick
======================
Office_ID char(5)
Zip char(5)
Distrib int

How would this change the code?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.