Actually, the very simplest of round robin implementations (not quite
perfect, but usually good enough) simply uses a sequential Job Number and
modulo by the number of users - 1:
lJobNumber = ...
sSQL = "SELECT UserID From Workers Where Status = 'Active' Order By UserID"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sSQL, cn, adOpenForwardOnly, adLockReadOnly
vaActiveUserIDs = rs.GetRows()
lActiveUserOffset = (lJobNumber Mod (Count(ActiveUserNo) - 1))
lRealUserID = vaActiveUserIDs(lActiveUserOffset)
With SQL Server (or another full-fledged DBMS, i.e. not with MS Access,
maybe not mySQL), you can define an insert (and Update if you wish) trigger
on the Jobs table, and assign the job to a user when it is created, if you
wish. However, if you assign a job prior to it actually being done, you
will run into situations where people get sick, leave, etc. while they have
unfinished jobs assigned to them.
Most workflow systems use Roles for workers and Queues for jobs. New jobs
get added to the appropriate job queue, and workers request their "next" job
which are then automatically picked from the queue(s) that is assigned to
their respective roles. Once the worker finishes the job or task, the job
may be assigned to the next queue in the workflow (and this you may not
need). Additionally, the worker may suspend unfinished work by assigning it
to a "work in progress" queue, or by releasing it back to the queue where it
was picked up. This is useful for instance when leaving the office without
finishing the job(s) that were started.
HTH,
Tore.
-----Original Message-----
From: Ben Timby [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 28, 2002 3:22 PM
To: ActiveServerPages
Subject: RE: Rotation
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Wow, I did not think this thru all the way, if you add a user, then
they would get all jobs until their count matched your existing
users. Here is a new Sproc using real round robin. I added a
transaction too.
--
CREATE PROCEDURE spJobINS
@JobDesc TEXT
AS
SET NOCOUNT ON;
-- separate queries for readability:
DECLARE @LastUserID INT;
DECLARE @NextUserID INT;
-- transaction to fend off concurrency issues:
BEGIN TRANSACTION;
-- get whomever has the last job assigned to them:
SELECT @LastUserID = (
SELECT TOP 1 fkUserID
FROM tblJobs
ORDER BY Stamp DESC
);
-- get the next user
SELECT @NextUserID = (
SELECT MAX(U1.fkUserID)
FROM tblUsers
WHERE fkUserID > @LastUserID;
);
-- if they were the last user, roll to first:
IF @NextUserID IS NULL
SELECT @NextUserID = MIN(fkUserID)
FROM tblUsers;
-- assign them this job
INSERT INTO tblJobs (
fkUserID,
JobDesc
) VALUES (
@NextUserID,
@JobDesc
);
-- work is done, commit it:
COMMIT TRANSACTION;
SET NOCOUNT OFF;
GO
--
Ben Timby
Webexcellence
PH: 317.423.3548 x23
TF: 800.808.6332 x23
FX: 317.423.8735
[EMAIL PROTECTED]
www.webexc.com
- -----Original Message-----
From: Ben Timby [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 28, 2002 12:04 PM
To: ActiveServerPages
Subject: RE: Rotation
*** PGP Signature Status: bad
*** Signer: Ben Timby <[EMAIL PROTECTED]>
*** Signed: 8/28/2002 12:03:25 PM
*** Verified: 8/28/2002 12:14:33 PM
*** BEGIN PGP VERIFIED MESSAGE ***
Basically, round robin just does the following:
Given 4 users, 1, 2, 3, 4 and 6 jobs, you would get:
1: job1, job5
2: job2, job6
3: job3
4: job4
You could easily implement it like this:
tblJobs
--
pkJobID INT IDENTITY
fkUserID INT References tblUsers.pkUserID
JobDesc TEXT
Stamp DATETIME DEFAULT = GETDATE()
tblUsers
--
pkUserID INT IDENTITY
Username VARCHAR
Stored Procedure to insert job and assign to next user:
--
CREATE PROCEDURE spJobINS
@JobDesc TEXT
AS
SET NOCOUNT ON;
-- two queries for readability:
DECLARE @NextUserID INT;
-- get whomever has the least amount of jobs:
SELECT @NextUserID = (
SELECT TOP 1 fkUserID
FROM tblJobs
GROUP BY fkUserID
ORDER BY COUNT(*) ASC
);
-- assign them this job
INSERT INTO tblJobs (
fkUserID,
JobDesc
) VALUES (
@NextUserID,
@JobDesc
);
SET NOCOUNT OFF;
GO
--
This is a very simplistic approach, but should work well. You may
have problems if you have more than one Job w/ the same stamp.
Good luck.
Ben Timby
Webexcellence
PH: 317.423.3548 x23
TF: 800.808.6332 x23
FX: 317.423.8735
[EMAIL PROTECTED]
www.webexc.com
- -----Original Message-----
From: Zaid Abdur-Rahman [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 28, 2002 6:57 AM
To: ActiveServerPages
Subject: Re: Rotation
The assumption of every job being the same size and requiring the
same
expertise is correct. The jobs are simple to reply to a request. So I
think
this round-robin approach should work. Can you elaborate a bit more
on how
to implement?
ZAID
- ----- Original Message -----
From: "Bostrup, Tore" <[EMAIL PROTECTED]>
To: "ActiveServerPages" <[EMAIL PROTECTED]>
Sent: Wednesday, August 28, 2002 9:43 AM
Subject: RE: Rotation
> Assuming every job is the same "size" (location, time, expertise,
> and
effort
> required, etc.) and if all users work at the same speed, a
> round-robin approach *might* work. In reality, things usually
> aren't that simple.
>
> Why not use the concept of a queue of (similar) jobs for a role,
> and have user request the "next job" from "their" queue (based on
> their
> role/position/location) when they are ready for it?
>
> HTH,
> Tore.
>
> -----Original Message-----
> From: Zaid Abdur-Rahman [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, August 28, 2002 9:33 AM
> To: ActiveServerPages
> Subject: Rotation
>
>
> Hello All,
>
> I have an Access tables of users that is used to assign to jobs.
> What I am trying to accomplish is to rotate through the users
> selecting as
individual
> jobs come in. What is the preferred.... best, way of handling the
> rotation of users?
>
> Thanks for the time and knowledge.
>
> Zaid Abdur-Rahman
> [EMAIL PROTECTED]
>
>
> ---
> You are currently subscribed to activeserverpages as:
> [EMAIL PROTECTED] To unsubscribe send a blank email to
> %%email.unsub%%
>
> ---
> You are currently subscribed to activeserverpages as:
> [EMAIL PROTECTED] To unsubscribe send a blank email to
%%email.unsub%%
- ---
You are currently subscribed to activeserverpages as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
%%email.unsub%%
*** END PGP VERIFIED MESSAGE ***
- ---
You are currently subscribed to activeserverpages as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
%%email.unsub%%
-----BEGIN PGP SIGNATURE-----
Version: PGP 7.0.4
iQA/AwUBPW0ivfnby1cCm2Q8EQLmrQCg5A+E5RKuhFisyEQIx/2mijyOO4oAoNop
dBUoAIoHd9Ojjim/3r8xGq8v
=e4zE
-----END PGP SIGNATURE-----
---
You are currently subscribed to activeserverpages as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
%%email.unsub%%
---
You are currently subscribed to activeserverpages as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
