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?
 
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

0
 
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

0
 
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.