Round robin update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Round robin update

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-05-12 : 01:17:08

I have about 4000 prospects on my table and I have 5 different sales people (Jim, Mary, Pete, Bob, Kate) I'd like to update the 4000 rows (acctmgr field) in a round robin fashion so that the first one is assigned to Jim, the 2nd to Mary the third to Pete etc etc

is this possible?

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-05-12 : 01:44:11

Try the scripts,

create table prospects (id int,acctmgr varchar(50))
declare @C as int
set @C=1
while @C<=4000
begin
insert into prospects(id) values(@C)
set @C=@C+1
end

create table manager(acctmgr varchar(5))
insert into manager values('Jim')
insert into manager values('Mary')
insert into manager values('Pete')
insert into manager values('Bob')
insert into manager values('Kate')
select * from manager

select prospects.id,manager.acctmgr from prospects,manager
where ((prospects.id % 5)<>0 and ((prospects.id % 5)= manager.id)
or ((prospects.id % 5)=0 and manager.id=5)) order by prospects.id

update prospects set prospects.acctmgr=manager.acctmgr from prospects,manager
where ((prospects.id % 5)<>0 and ((prospects.id % 5)= manager.id)
or ((prospects.id % 5)=0 and manager.id=5))

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Kristen
Test

22859 Posts

Posted - 2010-05-12 : 02:59:40

Note that ID may have gaps, so it may not be entirely even-stevens

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-12 : 03:54:58

How about this?

UPDATE	f
SET	f.Manager =	CASE rowID % 5
				WHEN 0 THEN 'Jim'
				WHEN 1 THEN 'Mary'
				WHEN 2 THEN 'Pete'
				WHEN 3 THEN 'Bob'
				WHEN 4 THEN 'Kate'
			END
FROM	(
		SELECT	ROW_NUMBER() OVER (ORDER BY ID) - 1 AS rowID,
			Manager
		FROM	dbo.Prospects
		WHERE	Manager IS NULL
	) AS f

N 56°04'39.26"
E 12°55'05.63"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-05-12 : 04:19:50

Peso is still the man

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-05-13 : 00:52:13

Hi Thanks for this. (sorry this reply refers to the first response - i hadn't noticed the others when i wrote)

I have tried it though I don't really understand what its doing. I am a not very experienced with SQL programming. EIther way it didn't seem to work.
I can see the first bit creates a prospect table with 4000 rows.
I can see the second bit creates my acct mgr table
its the next 2 bits I don't get.

When i ran it didn't put the account manager in the prospet table.
Also the it seems to refer to a Manager.id column which i don't think exists

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-05-13 : 01:04:46

Hi I used Pesos example and it worked thanks.

Can you please tell me what the % sign is doing as I have only ever used that in searches as a wild card before.

Thanks a lot

Kristen
Test

22859 Posts

Posted - 2010-05-13 : 02:32:21

quote:
Originally posted by icw

Can you please tell me what the % sign is doing as I have only ever used that in searches as a wild card before.

Modulus operator - the "remainder" after doing integer division.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources