Round Robin Ticket Assignment in SQL with limit of active tickets?

Question

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

score 1 · Accepted Answer · answered Mar 29 '22 at 18:14

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;

db<>fiddle

Round Robin Ticket Assignment in SQL with limit of active tickets?

1 Answers1