I have to assign the tickets to employees in a round robin fashion with a threshold of 5 tickets to an employee.
Employees should not have more than 5 active tickets. While assigning the tickets we should check for the active tickets in his bucket.
Example I have a Tickets table
Table Tickets
| TicketId | AssignedTo |
| -------- | -------------- |
| 1 | |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
| 6 | |
| 7 | |
| 8 | |
| 9 | |
| 10 | |
| 11 | |
| 12 | |
Employee table
| EmployeeId | ActiveTickets |
| -------- | -------------- |
| 123 | 4 |
| 124 | 0 |
| 125 | 3 |
| 126 | 1 |
| 127 | 1 |
As the Employee 123 has 4 active tickets while assigning the ticket he should be assigned only one ticket from the ticket table.
Result should be like below
Table Tickets
| TicketId | AssignedTo |
| -------- | -------------- |
| 1 | 123 |
| 2 | 124 |
| 3 | 125 |
| 4 | 126 |
| 5 | 127 |
| 6 | 124 |
| 7 | 125 |
| 8 | 126 |
| 9 | 127 |
| 10 | 124 |
| 11 | 126 |
| 12 | 127 |
Using the below query I was able achieve the round robin assignment of tickets but not sure how to set the threshold for active tickets.
WITH с AS
(
SELECT *, ROW_NUMBER() OVER ORDER BY (TicketId) AS rn
FROM Tickets
),
s AS
SELECT *,
ROW_NUMBER() OVER ORDER BY (EmployeeId) AS rn
FROM Employee
)
SELECT c.*, s.*
FROM с
JOIN s
ON s.rn =
(с.rn - 1) %
(
SELECT COUNT(*)
FROM Employee
) + 1
You can create a queue of all "free instances" of employees using a recursive cte. The query to get new assignments:
with cte as (
select e.EmployeeId, count(t.TicketId) n
from Empl e
left join Tickets t on t.AssignedTo = e.EmployeeId
group by e.EmployeeId
having count(t.TicketId) < 5
union all
select EmployeeId, n+1
from cte
where n < 4
), EQueue as (
select EmployeeId, n, row_number() over(order by n, EmployeeId) rn
from cte
), TQueue as (
select TicketId, row_number() over(order by TicketId) rn
from Tickets
where AssignedTo is null
)
select t.TicketId, e.EmployeeId AssignedTo
from EQueue e
join TQueue t on e.rn = t.rn;