Need Opinion-brain fried - 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 2000 Forums

Transact-SQL (2000)

Need Opinion-brain fried

Author

Topic

Rita
Starting Member

4 Posts

Posted - 2004-04-14 : 10:06:28

I need an opinion on the best way to handle an issue. I have looked at doing this so many different ways my eyes are now crossed and none of them seem to work quite the way I want. May it is from looking at this too long.

I have a table that I need to build a work queue off of. I need to assign certain individuals a record based on value in the table. The value identifies the record that needs to be worked (such as a record to be called back). I will be using the individual's employee id to assign the record to their queue.

So I have the following

Employee
123 Bob
456 Jane

Work table
1 I need to be worked
2 I need to be worked too
3 I need to be worked three

I want to be able to split the rows evenly to the employees, so they have an even amount of work. Example Bob would get rows 1 and 3, Jane would get 2 and 4. Now I could have more than two employees working the records. So say I throw in John employee id 789. The records would need to be assigned respectively:

record 1 - Bob
record 2 - Jane
record 3 - John.

So the number of employee's is variable and so is the number of records they would be working on. It will not be the same amount everyday.

Does anyone have any suggestions on how to handle this? I have explored creating a temp table and such but I would like to avoid this if possible.

Any help would be greatly appreciated.

Thanks.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-04-14 : 10:21:56

Have done something similiar in work here....have distributed the work....based on the person who is longest without work....

3 people (person code, last date time work allocated)
A 1/1/04 10am
B 1/1/04 11am
C 2/1/04 9am

work-items and who they get allocted to.
1.....will be assigned to A @ 14/4/04 11am
2.....will be assigned to B @ 14/4/04 12am
3.....will be assigned to C @ 14/4/04 2pm
new team member added D (no prior work history, naturally)
4.....will be assigned to D
5.....will be assigned to A
6.....will be assigned to B
etc......

this balances the load....i support out of the office-type scenarios....so that B can leave for hols, and the work spreads evenly over the remaining team-players....

I don't understand your retrospective requirement....how do you re-allocate work after it has been actioned...surely it can only happen on a "from now-on" basis?

Rita
Starting Member

4 Posts

Posted - 2004-04-14 : 10:38:29

Thank you for the reply. Sorry maybe I was not clear.

I have a table where customers require a call back. I have a group of individuals that will be working these call back records. We receive a file from an outside group where customers want to be contacted.

I need to distribute these customer leads evenly to the individuals working them (so I don't have someone whining about someone else having more records to work then they do). I wanted to use the individual's id to reference the record.

What would be the best way to handle this? What would be the best way to assign the record to the individual. I don't just want to assign it to a group, I then run a risk of 2 individuals calling the same person back at the same time?

Any thoughts on how to handle this are appreciated.

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-14 : 11:40:45

Why don't you just pull next from the table?

Assign the user to it, and give more money to the harder workers?

Or a talking too, to the slackers?

I would vote against this type of distribution...I've seen many of these things...

Some rocket scientists can come up with some Ivy League stuff...

And when we wrote the mess, it wan't important anymore, because we automated the hell out of the process...

See they got wrapped in the users request...because the work was so laborious, they (the manager) wanted an even distribution...

We got rid of the problem, and the contractor

My best advice is to keep it simple....and use a carrot or a hammer when needed....

Just one question though

quote:

The value identifies the record that needs to be worked

What does that mean...many types of actions?

If so, don't they have to be trained to do each job?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-14 : 11:41:22

Now how about a 'Rita?

Salt or no Salt?

Brett

8-)

Rita
Starting Member

4 Posts

Posted - 2004-04-14 : 11:53:18

Hi Brett,

Thanks for the reply. I will keep it simple. I think I was trying to get to complicated. Unfortunately I have a tendency to do that.

As for the Salt...well that would be up to the user.

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-14 : 12:11:08

I imagine the clerks sit at a browser...

Just make a sproc that does the assignment when they need more work...

BEGIN TRAN

SELECT TOP 1 * FROM myTable99 Where UserId IS Null

UPDATE myTable99 SET UserId = @UserID
WHERE keyCols = @keyCols
AND UserId Is NUll

-- Do much more formal error handling...but

IF @@ROWCOUNT=0
--Oops, someone got in before the update...Need to try again, or display
-- a message and the user can try again
BEGIN
ROLLBACK
END
ELSE
COMMIT

Something like that

Wedge of Lime?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-14 : 12:15:23

You know..this might even help avoid collisions...

SELECT TOP 1 * FROM myTable99 Where UserId IS Null ORDER BY NewId()

Usually though there is a date the work was added, and it's usually done in that order...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-14 : 12:26:42

Here's a working model...

Just cut and paste it in to QA to see the example execute....

Anyone see any downside to this?


USE Northwind
GO

CREATE TABLE myTable99(WorkId int PRIMARY KEY, UserId int)
GO

INSERT INTO myTable99(WorkId)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
GO

CREATE PROC mySproc99
	@UserId int
AS

  BEGIN TRAN
	DECLARE @WorkId int

	SELECT TOP 1 @WorkId = WorkId 
	  FROM myTable99 
	 WHERE UserId IS Null

	UPDATE myTable99 SET UserId = @UserID 
	 WHERE WorkId = @WorkId
	   AND UserId Is NUll 

	IF @@ROWCOUNT=0
	  BEGIN 
		ROLLBACK 
	  END
	 ELSE
	  BEGIN
		COMMIT
	  END
GO

EXEC mySproc99 123

SELECT * FROM myTable99

EXEC mySproc99 123

SELECT * FROM myTable99
GO

DROP PROC mySproc99
DROP TABLE myTable99
GO

Brett

8-)

Rita
Starting Member

4 Posts

Posted - 2004-04-14 : 13:09:22

Hi Brett,

Thanks a million. That works great.

And the wedge of lime? Well, I am sure it could be included if applied in the right way.

Thank you again for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources