Not to be condescending--and, in fact, it took me a while to think about
avoiding cursors--but the power of the relational system is that it is
SET-THEORETIC. Set based operations are ALWAYS faster than procedural
ones.
In any introductory Set Theory book or class, one of the first things
you learn is the concept of an Equivalence Class. From this, you learn
you can map the set of integers Z to any subset of that set, say N,
using Modular arithmetic.
To apply this here, your Merchants is the larger set (Z) and the
Analysts is the subset (N). The mapping is then [C] = Z mod N +
1--where the + 1 is to reindex the classes [C] from 1 to N instead of
the natural 0 to N - 1 from the modulo function.
The trick is to make sure the Analyst IDs are sequentially ordered from
1 to N. You can accomplish this through the DBCC CHECKIDENT function
with the RESEED option on the tblAnalyst table. There is one got'cha
with this function: you set the RESEED value to 0 if you want the next
entry to be assigned the value of 1 only if you've used the DELETE
statement to remove the analyst records; if you use the TRUCATE TABLE
statement, you would use a RESEED value of 1.
So, the following should work for you:
DECLARE @intMaxAnalystID AS INT
DBCC CHECKIDENT('tblAnalyst', RESEED, 0)
-- Load both tables.
SET @intMaxAnalystID =
(SELECT MAX(AnalystID)
FROM tblAnalyst
)
UPDATE MerchList
SET AnalystID = a.AnalystID
FROM tblAnalyst AS a
INNER JOIN
MerchList AS m
ON a.AnalystID = (m.MerchantListID % @intMaxAnalystID) +
1
Good luck.
Sincerely,
Anthony Thomas, MCDBA, MCSA
-----Original Message-----
From: Carter Phillip
[mailto:mssqldba-ezmlmshield-x85811415.[Email address protected]
Sent: Friday, July 30, 2004 2:06 AM
To: LazyDBA Discussion
Subject: RE: Assign Records to analysts
That looks promising.
If you want to remove the cursor, use this
-- get analyst ID
SELECT @Cntr = MIN(AnalystID)
FROM #Analyst
LEFT JOIN #MerchList
ON #Analyst.AnalystID = #MerchList.AnalystID
WHERE #MerchList.#MerchListID IS NULL
WHILE @Cntr IS NOT NULL
-- perform update
-- get next analyst ID
SELECT @Cntr = MIN(AnalystID)
FROM #Analyst
LEFT JOIN #MerchList
ON #Analyst.AnalystID = #MerchList.AnalystID
WHERE #MerchList.#MerchListID IS NULL
AND #Analyst.AnalystID > @Cntr
END
I say roll on SQL Server 2005 where you can use a varible as part of the
TOP
clause.
Thanks
Phillip Carter
Ph: +61 3 9235 1691
-----Original Message-----
From: Daniel Morphett
[mailto:mssqldba-ezmlmshield-x98390796.[Email address protected]
Sent: Friday, 30 July 2004 4:55 PM
To: LazyDBA Discussion
Subject: RE: Assign Records to analysts
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]
---------------------------------------------------------------------
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]
IMPORTANT DISCLAIMER - THIS MAY AFFECT YOUR LEGAL RIGHTS:
Because this document has been prepared without consideration of any
specific clients investment objectives, financial situation or needs,
a Bell Potter Securities Limited investment adviser should be
consulted before any investment decision is made.
While this document is based on the information from sources which
are considered reliable, Bell Potter Securities Limited, its directors,
employees and consultants do not represent, warrant or guarantee,
expressly or impliedly, that the information contained in this document
is complete or accurate.
Nor does Bell Potter Securities Limited accept any responsibility to
inform you of any matter that subsequently comes to its notice, which
may affect any of the information contained in this document.
This document is a private communication to clients and is not intended
for public circulation or for the use of any third party, without the
prior approval of Bell Potter Securities.
Disclosure of Interest: Bell Potter Securities Limited receives
commission
from dealing in securities and its authorised representatives, or
introducers
of business, may directly share in this commission. Bell Potter
Securities
and its associates may hold shares in the companies recommended.
Bell Potter Securities Limited ABN 25 006 390 772 AFS Licence No.
243480
---------------------------------------------------------------------
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]
***********************************************************************
NOTICE: This electronic mail message and any attached files are
confidential. The information is exclusively for the use of the
individual or entity intended as the recipient. If you are not the
intended recipient, any use, copying, printing, reviewing, retention,
disclosure, distribution or forwarding of the message or any attached
file is not authorized and is strictly prohibited. If you have received
this electronic mail message in error, please advise the sender by reply
electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your
computer system.
***********************************************************************
MS Sql Server LazyDBA home page