Assign user name to task on Round Robin approach

Transact-SQL
1

Hi - Below are the list of tables and task table is transaction table and user table is config table.
When we pick the task, user need to be assigned to each task on round robin basis. Any suggestion how to achive this through sql logic

|Task||||

TaskId | TaskNumber | WorkOrder | username
|1|102|9090||
|2|105|9093||
|3|107|9094||
|4|109|9095||
|5|111|9098||

User Table
userid | username
|1|user1
|2|user2|

Output Table

TaskId | TaskNumber | WorkOrder | username
|1|102|9090|user1
|2|105|9093|user2
|3|107|9094|user1
|4|109|9095|user2
|5|111|9098|user1

2

hi hope this helps

my solution assumes there are only 2 users

create data script

create table #Task ( TaskID int , TaskNumber varchar(10) , WorkOrder int )
insert into #Task select 1,'102',9090
insert into #Task select 2,'105',9093
insert into #Task select 3,'107',9094
insert into #Task select 4,'109',9095
insert into #Task select 5,'111',9098
select * from #Task

create table #User (userid int, username varchar(10))
insert into #User select 1,'user1'
insert into #User select 2,'user2'
select * from #User

select 
      a.TaskId
   ,  a.TaskNumber
   ,  a.WorkOrder 
   ,  b.username  
from 
    #Task a 
	  join 
    #User b 
	    on case when a.TaskId%2 = 0 then 2 else 1 end = b.userid

image

3

hi

this is a scenario if there are 3 users

if there are 4 users ..change it to
on case when a.TaskId%4 = 0 then 4 else a.TaskId%4 end = b.userid

obviously .. 3 or 4 is hard coded .. it can be made dynamic ... depends on your requirements

create data script

create table #Task ( TaskID int , TaskNumber varchar(10) , WorkOrder int )
insert into #Task select 1,'102',9090
insert into #Task select 2,'105',9093
insert into #Task select 3,'107',9094
insert into #Task select 4,'109',9095
insert into #Task select 5,'111',9098
insert into #Task select 6,'115',9097

create table #User (userid int, username varchar(10))
insert into #User select 1,'user1'
insert into #User select 2,'user2'
insert into #User select 3,'user3'

select 
      a.TaskId
   ,  a.TaskNumber
   ,  a.WorkOrder 
   ,  b.username  
from 
    #Task a 
	  join 
    #User b 
	    on case when a.TaskId%3 = 0 then 3 else a.TaskId%3 end = b.userid

image
image789×708 12.8 KB

4 

;WITH cte_tasks AS (
    SELECT *, ROW_NUMBER() OVER(ORDER BY TaskID) AS row_num
    FROM #Task
),
cte_users AS (
    SELECT *, ROW_NUMBER() OVER(ORDER BY userid) AS row_num
    FROM #User
),
cte_total_users AS (
    SELECT COUNT(*) AS total_users
    FROM cte_users
)
SELECT *
FROM cte_tasks ct
INNER JOIN cte_users cu ON cu.row_num = ct.row_num % 
    ((SELECT total_users FROM cte_total_users) + 1)
ORDER BY ct.TaskId