I am using an Access DB so I can not use the Sproc. However, I get the idea of what you are doing.
Thanks ZAID ----- Original Message ----- From: "Ben Timby" <[EMAIL PROTECTED]> To: "ActiveServerPages" <[EMAIL PROTECTED]> Sent: Wednesday, August 28, 2002 3:21 PM 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]
