?
Solved

Function to implement 'round-robin' selection?

Posted on 2008-11-13
3
Medium Priority
?
297 Views
Last Modified: 2012-05-05
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?
0
Comment
Question by:Chuck Brown
  • 2
3 Comments
 
LVL 9

Expert Comment

by:Ernariash
ID: 22954164

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
 
LVL 9

Accepted Solution

by:
Ernariash earned 2000 total points
ID: 22954267
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
 

Author Comment

by:Chuck Brown
ID: 23089862
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Screencast - Getting to Know the Pipeline

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question