help add column of repeating numbers 123412341234

Question

Post reply

help add column of repeating numbers 123412341234

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

joeroshan SSChampion Points: 10377 More actions · Answer 1

Tim Wilson-Brown (12/3/2009)

The big question is - what happens when there are 5 salespeople?

Tim, what you suggested will work for 5 people also, I think.

Declare @rank_test table



(

id int identity(1,1),

data varchar(250),

sales_man int null

) 

insert into @rank_test(data)

select top 61 name from syscolumns 

/*

-- My solution.

-- For the first salesman

update @rank_test set sales_man=1  where  (id+4)%5=0

-- For the second salesman

update @rank_test set sales_man=2  where  (id+3)%5=0

-- For the third salesman

update @rank_test set sales_man=3  where  (id+2)%5=0

update @rank_test set sales_man=4  where  (id+1)%5=0

update @rank_test set sales_man=5  where  (id)%5=0

select * from @rank_test

*/

update @rank_test set sales_man=

(id%5)+1

select * from @rank_test

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Tim Wilson-Brown Ten Centuries Points: 1395 More actions · Answer 2

joeroshan (12/3/2009)

Tim Wilson-Brown (12/3/2009)

The big question is - what happens when there are 5 salespeople?

Tim, what you suggested will work for 5 people also, I think.

...

I guess I should have been more specific...

What happens if:

You design the system for 4 salespeople, then, a year later, you employ an additional salesperson?

How do you ensure that the new salesperson gets a 'fair' number of leads?

Do you want to avoid redistributing all the leads? (This could disrupt existing business relationships)

Which ones do you pick to redistribute? Newest? Lowest value?

Better to design for this situation straight-up, than have to perform additional design & implementation before you can bring on additional staff members.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Heh... look back folks... the OP has left the building. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions