November 29, 2009 at 10:25 pm
I have a table of sales leads that is ordered by quality of the leads. I need to add a column that repeats a series of numbers 1,2,3,4,1,2,3,4,... to allocate the leads fairly to 4 sales people. All 1s go to salesman A, 2s go to salesman B, etc. Can anyone point me in the right direction on this?
Thanks,
Bill
November 29, 2009 at 10:28 pm
There is not much to say rather than giving a small piece of advice.
Look a Pivot / Pivoting in SQL Server 2005. Look some examples and may be you find what you meed.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 30, 2009 at 12:42 am
This may not be optimal, but might help you
Declare @rank_test table
(
id int identity(1,1),
data varchar(250),
sales_man int null
)
insert into @rank_test(data)
select top 60 name from syscolumns
-- For the first salesman
update @rank_test set sales_man=1 where (id+3)%4=0
-- For the second salesman
update @rank_test set sales_man=2 where (id+2)%4=0
-- For the third salesman
update @rank_test set sales_man=3 where (id+1)%4=0
-- For the fourth salesman
update @rank_test set sales_man=4 where (id)%4=0
select * from @rank_test
November 30, 2009 at 8:50 am
Joe's code could also be made into one statement like this:
update @rank_test
set sales_man=
CASE WHEN (id+3)%4=0 THEN 1
WHEN (id+2)%4=0 THEN 2
WHEN (id+1)%4=0 THEN 3
ELSE 4
END
You could also use a persisted calculated column for this which would likely suit your needs better than an update. Using Joe's table variable and modulo concept, that would look something like this:
Declare @rank_test table
(
id int identity(1,1),
data varchar(250),
sales_man AS (CASE WHEN (id+3)%4=0 THEN 1
WHEN (id+2)%4=0 THEN 2
WHEN (id+1)%4=0 THEN 3
ELSE 4
END) PERSISTED
)
insert into @rank_test(data)
select top 60 name from syscolumns
select * from @rank_test
November 30, 2009 at 5:49 pm
Joe's code is pretty good for this - build a temporary set with some Identity-type column. Div or Mod that appropriately and compare the remainders. (I'd probably do integer division, get the remainder, and compare that remainder, but that's more of a preference.)
It has the advantage that if you need to re-allocate some set number of items, you can change your query relatively easily to do so. (Such as if a salesperson leaves.)
You could even pull the sales people into their own temp table with an Identity value and use that to reallocate based on who's active. The only thing I'd add to this is to check the current allocation levels before running the balancing routine. I'd probably group by the number of active assignments per person, ordered so those with the fewest get assigned first. I've written something like this before, but don't recall the exact details. The process was something like:
1. Get all eligible people w/ their current counts.
2. If they are not equal, distribute work (round-robin) until they are all equal.
3. When all equal, distribute in a round-robin fashion to finish allocating the rest of the work.
December 1, 2009 at 8:12 am
Here is another solution that doesn't require an identity column:
select name, LeadQuality, CASE WHEN (LeadOrder+3)%4=0 THEN 1
WHEN (LeadOrder+2)%4=0 THEN 2
WHEN (LeadOrder+1)%4=0 THEN 3
ELSE 4
END as sales_man
from (select top 60
name, id as LeadQuality, row_number () OVER (ORDER BY ID) as LeadOrder
from sysobjects where id > 0) as t
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 1, 2009 at 9:24 am
Keep forgetting about the Row_Number function, even though I've used it. SQL 2000 and lower habits are pretty deeply entrenched. 😛
Thanks for the reminder about that one.
December 1, 2009 at 9:28 am
Peter Schott (12/1/2009)
Keep forgetting about the Row_Number function, even though I've used it. SQL 2000 and lower habits are pretty deeply entrenched. 😛Thanks for the reminder about that one.
I too need to beef up my skills with the ranking and OVER stuff, because it is so powerful and flexible and can be used for numerous problems. I really hope MS adds in more of the windowing functionality so we can do things like running totals with it!! The MVPs on the private forum have been begging MS for years on this...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 1, 2009 at 9:54 am
TheSQLGuru (12/1/2009)
Peter Schott (12/1/2009)
Keep forgetting about the Row_Number function, even though I've used it. SQL 2000 and lower habits are pretty deeply entrenched. 😛Thanks for the reminder about that one.
I too need to beef up my skills with the ranking and OVER stuff, because it is so powerful and flexible and can be used for numerous problems. I really hope MS adds in more of the windowing functionality so we can do things like running totals with it!! The MVPs on the private forum have been begging MS for years on this...
By the way - since we're talking new features, there's a builti-in function to do exactly this. Check out NTILE().
select *, NTILE(4) over (order by n) Quartile --4 because you mentioned 4 groups
from tally
if you want a random order, use ORDER BY NEWID(), although that will take a chunk out of your performance.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 1, 2009 at 10:02 am
That's cool. Didn't know about the NTILE function. Wish I'd had that about 6 years ago. It would have made my life a lot easier. Of course, we would have had to be on SQL 2005, but it's still good to know now. Adding to my list of tricks.
Thanks.
December 1, 2009 at 10:16 am
I didn't use the NTILE function because it seemed the OP wanted the 'buckets' of leads per salesperson to be evenly distributed in desc order. Something semi-random such as NEWID won't accomplish that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 1, 2009 at 10:21 am
But you could combine that with a listing of the Sales people using a RowNumber or Identity type value and tie them together if I'm reading the solution and problem correctly. It would need some tweaking, but I think it could work.
Regardless, distributing evenly over the existing people is probably a concern. Should also consider how many items are being worked by each person as well. Someone with a lot in the queue should not get the same number dumped on him/her as someone with an empty queue (at least in most business cases).
December 1, 2009 at 10:33 am
TheSQLGuru (12/1/2009)
I didn't use the NTILE function because it seemed the OP wanted the 'buckets' of leads per salesperson to be evenly distributed in desc order. Something semi-random such as NEWID won't accomplish that.
Correct- if the "physical order" is key, then your row_number setup will work best. NTILE does split evenly (inasmuch as it's possible to splt evenly), just not in a round robin fashion.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 3, 2009 at 4:02 pm
CASE WHEN (id+3)%4=0 THEN 1
WHEN (id+2)%4=0 THEN 2
WHEN (id+1)%4=0 THEN 3
ELSE 4
END
can easily be replaced with (id%4) + 1 to achieve exactly the same result.
The big question is - what happens when there are 5 salespeople?
December 3, 2009 at 6:20 pm
Tim - some questions shouldn't be asked!! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply