-----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 PROTECTED]
