Assign records to Analysts

[Vishal Sinha]

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

[Simon Hayes]

"Vishal Sinha" <vs******@yahoo .com> wrote in message
news:2f******** *************** ***@posting.goo gle.com...

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

Your requirements aren't entirely clear (at least to me), and the tables
don't seem to have enough information (keys and constraints) to give a good
answer. Is the MerchList table repopulated every day, or are today's
merchants identified by FileDate? Can the same MerchID appear multiple times
in one day (if that is what FileDate is for)? Is the tblAnalyst table
repopulated every day? If so, then how do you track AnalystIDs over time? If
it is not, then how do you mark analysts as sick or unavailable (there seems
to be no column for that)?

Your basic requirement seems to be assigning merchants to analysts, but it
isn't clear what you mean by "equally". What happens if some analysts can
process more merchants than others, due to greater experience, better
resources or whatever? In that case, you would want to weight the
assignment, so they get more merchants assigned to them. Or what happens if
the numbers simply don't divide into each other cleanly - who gets the
'extra' merchants?

And do you have to ensure that merchants are 'rotated' among analysts, or is
it acceptable for the same merchant to be assigned to the same analyst every
day indefinitely? If you need rotation, should it be random or periodic? Are
there rules about merchants which can never be assigned to certain
analysts - a blacklist or Chinese wall?

I don't want to sound unhelpful, but I suspect you may be oversimplifying
your requirements, and if you do need to consider some of the issues I've
suggested, then it's likely that you'll end up with something too detailed
to discuss in a newsgroup.

But if it is a simple case, then I think you'll get more help by providing
some sample data, preferably for at least a couple of different days. That
should help clarify what you expect to achieve.

Simon

[Vishal Sinha]

Thanks for the quick response,
Please see my replies below:

Re: Assign records to Analysts
From: Simon Hayes
Date Posted: 1/12/2004 1:43:00 PM
Your requirements aren't entirely clear (at least to me), and the tables
don't seem to have enough information (keys and constraints) to give a
good answer. Is the MerchList table repopulated every day, or are
today's merchants identified by FileDate? Can the same MerchID appear
multiple times in one day (if that is what FileDate is for)? Is the
tblAnalyst table repopulated every day? If so, then how do you track
AnalystIDs over time? If
it is not, then how do you mark analysts as sick or unavailable (there
seems to be no column for that)?

Vishal>> There is one record per mechant and the list is created on a
daily basis. so the table would have data for multiple dates where there
can be only one record for a merchant for any given date.

Your basic requirement seems to be assigning merchants to analysts, but
it isn't clear what you mean by "equally". What happens if some analysts
can process more merchants than others, due to greater experience,
better
resources or whatever? In that case, you would want to weight the
assignment, so they get more merchants assigned to them. Or what happens
if the numbers simply don't divide into each other cleanly - who gets
the
'extra' merchants?

Vishal>> I would need to assign equal number of merchants to analysts.
If the numbers do not divide equally then the first few get the extra
(can be order by ID - does not matter)

And do you have to ensure that merchants are 'rotated' among analysts,
or is it acceptable for the same merchant to be assigned to the same
analyst every day indefinitely? If you need rotation, should it be
random or periodic? Are
there rules about merchants which can never be assigned to certain
analysts - a blacklist or Chinese wall?

Vishal>> No rotation required

I don't want to sound unhelpful, but I suspect you may be
oversimplifying
your requirements, and if you do need to consider some of the issues
I've
suggested, then it's likely that you'll end up with something too
detailed
to discuss in a newsgroup.

But if it is a simple case, then I think you'll get more help by
providing
some sample data, preferably for at least a couple of different days.
That
should help clarify what you expect to achieve.

Simon
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Vishal Sinha (vs******@yahoo .com) writes:

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.

If I understood the requirements from your supplemental posting
correctly, this could be a solution:

UPDATE MerchList
SET AnalystID = MerchListID %
(SELECT COUNT(*) FROM tblAnalyst) + 1

This presumes that the AnalystIDs goes from 1 to 25 (or whatever)
without gaps. If there are gaps, or the id:s do not start at 1,
you could do:

CREATE TABLE #analystmap (id int IDENTITY(0, 1),
analystid int NOT NULL

INSERT #analystmap (analystid)
SELECT AnalystID FROM tblAnalyst ORDER BY newid()

UPDATE MerchList
SET AnalystID = a.analystid
FROM MerchList m
JOIN #analystmap a ON
m.MerchList % (SELECT COUNT(*) FROM #analystmap) = a.id

The ORDER BY newid() adds a certain randomness into the process, but
still merchants that are 25 ids apart will get the same analyst.

Disclaimer: as there was not test data included, I have not tested this.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

Your schema has no keys, no DRI and no constraints. You will also
have no data integrity. Let's fix it up:
CREATE TABLE MerchAnalysts
(file_date DATETIME NOT NULL,
REFERENCES Analysts (analyst_id),
merch_id INTEGER NOT NULL
REFERENCES Merchants (merch_id),
analyst_id INTEGER NOT NULL,
REFERENCES Analysts (analyst_id),
..
PRIMARY KEY (file_date, analyst_id, merch_id));

Since I do not know what the **real keys** for merchants and analysts
would be (SSN? Tax number, DUN number?), I have left them as
INTEGERs; fix this omission immediately! IDENTITY is never a key. I
also shortened the names to a length that will not collect garbage
strings or be so long they cannot display easily, etc.

CREATE TABLE Analysts
(analyst_id INTEGER NOT NULL PRIMARY KEY,
analyst_name VARCHAR(35) NOT NULL,
..);

CREATE TABLE Merchants
(merch_id INTEGER NOT NULL PRIMARY KEY,
analyst_name VARCHAR(35) NOT NULL,
..);

I want to assign each merchant with an analyst daily and need help

to write out a stored procedure for this <<

What is the rule for the assignments? To write a procedure, we need
to have a spec. Do you want to divide them up evenly each day? Are
certain analysts assigned to certain merchants, but have an
alternative analyst if the primary one is not at work? Are the rules
more complex than that either of those possibilities?

[Vishal Sinha]

Thanks for the Suggestions - it is EXACTLY what I needed. I did post a
rather simplified version of table design for illustration purposes.

Thanks everyone!

Vishal
Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>...

Vishal Sinha (vs******@yahoo .com) writes:

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.

If I understood the requirements from your supplemental posting
correctly, this could be a solution:

UPDATE MerchList
SET AnalystID = MerchListID %
(SELECT COUNT(*) FROM tblAnalyst) + 1

This presumes that the AnalystIDs goes from 1 to 25 (or whatever)
without gaps. If there are gaps, or the id:s do not start at 1,
you could do:

CREATE TABLE #analystmap (id int IDENTITY(0, 1),
analystid int NOT NULL

INSERT #analystmap (analystid)
SELECT AnalystID FROM tblAnalyst ORDER BY newid()

UPDATE MerchList
SET AnalystID = a.analystid
FROM MerchList m
JOIN #analystmap a ON
m.MerchList % (SELECT COUNT(*) FROM #analystmap) = a.id

The ORDER BY newid() adds a certain randomness into the process, but
still merchants that are 25 ids apart will get the same analyst.

Disclaimer: as there was not test data included, I have not tested this.