1

We need to write some code that will assign sales reps to our customers, but the percentages are not equal. Based on total purchases, we want to assign the sales reps to the customer based on this scale:

Sales Rep 1 - 21%
Sales Rep 2 - 21%
Sales Rep 3 - 17%
Sales Rep 4 - 17%
Sales Rep 5 - 15%
Sales Rep 6 - 9%

We assume some round robin logic will be needed. But how to assign the rep to the customer based on non-even percentages like that? So, if we have say 100 total orders, and then a new purchase makes it 101, then another purchase makes it 102, etc.

Any help would be great.

Landon Statis
  • 683
  • 2
  • 10
  • 25
  • Can you provide what outcome you want to see? – James Cooke Apr 13 '23 at 16:35
  • You're talking about customers, orders and total purchases, which all seem to be different (if related) things. Presumably for each new customer? order? you'll need to count the existing ones for each rep, and maybe figure out the current percentages, and then decide who to assign - perhaps the lowest actual vs target percentage. But you need to define the rules and logic to use. You're asking how to do something in PL/SQL when you haven't defined, for us or yourself, *what* you actually want to do. – Alex Poole Apr 13 '23 at 16:37
  • Ok, sorry if it was not explained better. So, yes, the customer places an order. We want to assign a sales rep to that order based on the uneven percentages (from above) of total orders. So, I'm guessing that when an order is placed, I need to perhaps get the total number of orders, along with the total number of orders assigned to each rep, and do some round robin to determine who is going to get assigned to the new order. Does that explain it a bit better? – Landon Statis Apr 13 '23 at 16:41
  • Statistical weighting is often used for unequal selection in research. You just need to define your weighting method. Then it won't be unfair. – JustBeingHelpful Apr 13 '23 at 16:43
  • This is different from your situation, but very similar in many ways. https://www.ovationmr.com/statistical-weighting-methods/ – JustBeingHelpful Apr 13 '23 at 16:45
  • 1
    @LandonStatis So, your particular problem is specifically about who to assign a single ***new*** item to (the *next* order), not how to assign a large number of existing items? If so, create a [mre] where you have your reps, your customers and a number of orders that have already been assigned, then state who should get the subsequent orders (in which sequence) and why. – MatBailie Apr 13 '23 at 16:52
  • Before this gets closed, this should solve your problem. https://stackoverflow.com/questions/58457/random-weighted-choice-in-t-sql – JustBeingHelpful Apr 13 '23 at 16:52

0 Answers0