Try this (obviously you'll need to tweak it a bit to fit your needs, but
it does the basic job):
CREATE TABLE #MerchList (
MerchListID int IDENTITY (1, 1) NOT NULL ,
FileDate datetime NOT NULL ,
MerchID int NOT NULL ,
AnalystID int NOT NULL ,
)
CREATE TABLE #tblAnalyst (
AnalystID int IDENTITY (1, 1) NOT NULL ,
AnalystName varchar(100) NOT NULL
)
declare @counter int
set @counter = 0
while @counter < 10005
begin
if (@counter < 26) and (@counter != 0 )
begin
insert into #tblAnalyst(analystname)
values ('Analyst' + cast(@counter as varchar))
end
insert into #MerchList(filedate,merchID,analystID) values
(getdate(),@counter,0)
set @counter = @counter + 1
end
select * from #MerchList
select * from #tblAnalyst
declare @numAnalysts int, @numMerchants int, @numMerchantsPerAnalyst
int, @cmd varchar(2000), @aid int, @MerchListID int
select @numAnalysts = count(*) from #tblAnalyst
select @numMerchants = count(*) from #MerchList
set @numMerchantsPerAnalyst = @numMerchants/@numAnalysts
print @numAnalysts
print @numMerchants
print @numMerchantsPerAnalyst
declare curs_analyst cursor for
select analystID from #tblAnalyst
open curs_analyst
fetch next from curs_analyst into @aid
while @@fetch_status = 0
begin
select @cmd = 'update #MerchList set analystID = ' + cast(@aid
as varchar) + ' where MerchListID in (select top ' +
cast(@numMerchantsPerAnalyst as varchar) + ' MerchListID from #MerchList
where analystID = 0) '
exec( @cmd )
fetch next from curs_analyst into @aid
end
close curs_analyst
deallocate curs_analyst
declare @remainder int
select @remainder = count(*) from #MerchList where analystID = 0
print @remainder
-- if any remainder, assign them to random analysts
if @remainder > 0
begin
declare curs_merch cursor for
select MerchListID from #MerchList where analystID = 0
open curs_merch
fetch next from curs_merch into @MerchListID
while @@fetch_status = 0
begin
set @cmd = 'update #merchlist set analystID =
(select cast(rand()* ' + cast(@numAnalysts as varchar) + ' as int))
where MerchListID = ' + cast(@MerchListID as varchar)
print @cmd
exec ( @cmd )
fetch next from curs_merch into @MerchListID
end
close curs_merch
deallocate curs_merch
end
select analystID, count(*) from #merchlist
group by analystID
Daniel Morphett
DBA
Brilliant Digital Entertainment
02 9281 9272
-----Original Message-----
From: Vishal Sinha
[mailto:mssqldba-ezmlmshield-x54596414.[Email address protected]
Sent: Friday, July 30, 2004 4:15 PM
To: LazyDBA Discussion
Subject: Assign Records to analysts
Here is my issue. I have a list of merchants
(generated daily) and I need to assign them eually
to
a set of analysts. Both the merchant list and
analyst
list can change daily.
I want to assign each merchant with an analyst daily
and need help to write out a stored procedure for
this
(do not want to use VB).
Here is relevant code for the tables:
CREATE TABLE MerchList (
MerchListID int IDENTITY (1, 1) NOT NULL ,
FileDate datetime NOT NULL ,
MerchID int NOT NULL ,
AnalystID int NOT NULL ,
)
CREATE TABLE tblAnalyst (
AnalystID int IDENTITY (1, 1) NOT NULL ,
AnalystName varchar(100) NOT NULL
)
there will be about 10000 records in table MerchList
and around 25 records in table tblAnalyst.
This will be used to assign work to analysts on a
daily basis.
Thanks for all your help!
Vishal
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page