By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * into Tickets
from(
values
(1 , null),
(2 , null),
(3 , 123),
(4 , null),
(5 , 124),
(6 , null),
(7 , null),
(8 , 123),
(9 , null),
(10, 123),
(11, null),
(12, null)
)t(TicketId, AssignedTo)
12 rows affected
select * into Empl
from(
values
(123),
(124),
(125),
(126),
(127)
)t(EmployeeId)
5 rows affected
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
| TicketId | AssignedTo |
|---|---|
| 1 | 125 |
| 2 | 126 |
| 4 | 127 |
| 6 | 124 |
| 7 | 125 |
| 9 | 126 |
| 11 | 127 |
| 12 | 124 |
Warning: Null value is eliminated by an aggregate or other SET operation.