April 4, 2012 at 5:39 am
Hi all,
there are two tables, one contains the users, the other contains jobs to be taken by the users.
My target is create a single statement to distribute the jobs to the agents. Here a simplified example:
-- create example table Jobs
CREATE TABLE Jobs (ID INT, Name NVARCHAR(16))
INSERT INTO Jobs VALUES (1,'Job A')
INSERT INTO Jobs VALUES (2,'Job b')
INSERT INTO Jobs VALUES (3,'Job C')
INSERT INTO Jobs VALUES (4,'Job D')
INSERT INTO Jobs VALUES (5,'Job E')
INSERT INTO Jobs VALUES (6,'Job F')
GO
DECLARE @concurrentJobs INT
SET @concurrentJobs = 2
-- this is the desired reult:
-- get for each user the next Jobs
-- because @concurrentJobs = 2: User A will get JobID 1 + 2 and User B will get 3 + 4
SELECT 1 AS UserID, 1 AS JobID UNION
SELECT 1 AS UserID, 2 AS JobID UNION
SELECT 2 AS UserID, 3 AS JobID UNION
SELECT 2 AS UserID, 4 AS JobID
GO
-- drop all tables
DROP TABLE Jobs
DROP TABLE Users
Can someone help me?
Thanx!
April 4, 2012 at 7:33 am
If JobID is sequential and always starting from 1, then you could use something like this:
-- create example table Jobs
CREATE TABLE #Jobs (ID INT, Name NVARCHAR(16))
INSERT INTO #Jobs VALUES (1,'Job A')
INSERT INTO #Jobs VALUES (2,'Job b')
INSERT INTO #Jobs VALUES (3,'Job C')
INSERT INTO #Jobs VALUES (4,'Job D')
INSERT INTO #Jobs VALUES (5,'Job E')
INSERT INTO #Jobs VALUES (6,'Job F')
GO
CREATE TABLE #Users (UserID INT IDENTITY(1,1) PRIMARY KEY, NAME VARCHAR(30))
INSERT INTO #Users
SELECT 'Bob'
UNION ALL
SELECT 'Jane'
UNION ALL
SELECT 'Billy'
UNION ALL
SELECT 'Fred'
UNION ALL
SELECT 'Claire'
DECLARE @concurrentJobs DECIMAL(18,10) --make this decimal rather than int so division is accurate
SET @concurrentJobs = 2
SELECT * FROM #Jobs J
INNER JOIN (SELECT ceiling(ROW_NUMBER() OVER (ORDER BY UserID) /@concurrentJobs) JobCounter, UserID FROM #Users) U ON U.JobCounter=J.ID
If JobID is not starting from 1 and there may be gaps (e.g. it's an identity), then you'd just have to use Row_Number() to give them a sequential number starting from 1 first.
April 4, 2012 at 8:13 am
if you are writing a custom program or want to write triggers on a table we do something similar to this. we have a waiting assignment table. the assignment gets put into this table with very limited columns (assignmentid, agentid) and each agent looks at the table pulls there assignment and deletes it from this table. in your jobs table i would add 2 columns (assigned bit, completed bit or datetime). the trigger or app would grab the first assignment (based however you determine) and insert it into assignment and mark it as assigned in the jobs table and assign it to the agent with the least ammount of jobs. in our situation we also have an agent in/out table where if an agent is marked as out they could not be assigned any new jobs.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 5, 2012 at 9:41 am
The code below will allocate the maximum concurrent jobs to the available users. Using NTILE with a select query will allow the jobs to be spread over the available users. A top in the first (inner) subquery will only allow a maximum of (users * concurrentjobs) to be selected for allocation.
-- create example table Jobs
CREATE TABLE #Jobs (ID INT, Name NVARCHAR(16))
INSERT INTO #Jobs VALUES (1,'Job A')
INSERT INTO #Jobs VALUES (2,'Job b')
INSERT INTO #Jobs VALUES (3,'Job C')
INSERT INTO #Jobs VALUES (4,'Job D')
INSERT INTO #Jobs VALUES (5,'Job E')
INSERT INTO #Jobs VALUES (6,'Job F')
GO
CREATE TABLE #Users (UserID int)
INSERT INTO #Users VALUES (1)
INSERT INTO #Users VALUES (2)
INSERT INTO #Users VALUES (3)
INSERT INTO #Users VALUES (4)
GO
CREATE TABLE #JobsAllocated(ID int, UserID int)
GO
DECLARE @MaxConcurrentJobs INT
SET @MaxConcurrentJobs = 2
INSERT INTO #JobsAllocated
SELECT NTILE((select count(*) from #Users)) over (order by ID ASC) as UserID, ID
FROM
(SELECT top (@MaxConcurrentJobs * (select count(*) from #Users)) *
FROM #Jobs
ORDER BY ID ASC) AS A
SELECT * FROM #JobsAllocated
GO
-- drop all tables
DROP TABLE #Jobs
DROP TABLE #Users
DROP TABLE #JobsAllocated
Fitz
April 6, 2012 at 1:33 am
CELKO, how does your method address the number of users available to assign the jobs to and the maximum concurrent jobs that need not be exceeded for each user during allocation? Or are you showing this as a general method that does not answer this specific question?
Fitz
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply