TSQL - using case statements with dynamic tables (unknown row counter)

Question

I had a problem trying to create a stored procedure. I am using Microsoft SQL Server Management Studio 2017 with T-SQL. I have 2 tables, eTasksand eStaff. Below are the columns for each table:

eStaff

StaffID | Name

eTasks

TaskID | StaffID | Title | CreateDate

Currently, as the data stands, all tasks are assigned to StaffID '1'. Both eTasks and eStaff tables are updated with new tasks and staff, or they are pulled out, these tables never have the same exact rows every day. Some days there will be 1000 rows in the eTask table, and the next one can only be 400. After a few days, there will be 3 employees in the eStaff table, and 12 in the next.

What I would like to do is to evenly distribute tasks among the current StaffIDs when executing my stored procedure.

So far this is what I have:

CREATE PROCEDURE UpdatingeTasksTable 
AS 
    DECLARE t_rowCount INTEGER
    DECLARE s_staffIDCount INTEGER

    SET t_rowCount = SELECT COUNT(*) FROM eTasks
    SET s_staffIDCount = SELECT DISTINCT StaffID FROM eStaff

    UPDATE eTasks
    SET StaffID = CASE WHEN TaskID % t_rowCount = 1 
                          THEN 1
                       WHEN TaskID % t_rowCount = 2 
                          THEN 4
                       WHEN TaskID % t_rowCount = 3 
                          THEN 3
                       WHEN TaskID % t_rowCount = 4 
                          THEN 2
                  END 
    FROM eTasks b
    WHERE TaskID = b.TaskID;

I know how my request is currently, it will share tasks among 4 people. Is there a way to make a dynamic operator CASEso that there is not just a set of static numbers?

Answer 1

You must do this using row_number():

with s as (
      select s.*, row_number() over (order by (select null)) as seqnum,
             count(*) over () as cnt
      from estaff
     ),
     t as (
      select t.*, row_number() over (order by (select null)) as seqnum
      from etasks
     )
update t
    set staffid = s.staffid
    from t join
         s
         on s.seqnum = (t.seqnum % s.cnt) + 1;

Answer 2

We can use NTILEfor a dynamic number of employees, for example:

ALTER PROCEDURE UpdatingeTasksTable 
AS 
BEGIN 
 DECLARE @t_rowCount INT;
 select @t_rowCount = COUNT(*) from eStaff;

 WITH CTE AS(
  select TaskID, 
   NTILE(@t_rowCount) OVER (ORDER BY TaskID) AS NTILE, 
   StaffID, Title, 
   CreateDate 
 FROM eTasks)
 UPDATE CTE
  SET CTE.StaffID=CTE.NTILE

END

Run SP

EXEC UpdatingeTasksTable

Check Results

select * from eTasks