TSQL - Using Case Statements with Dynamic tables (unknown row count)

Question

I'm having a bit of an issue with trying to create a stored procedure. I'm using Microsoft SQL Server Management Studio 2017 with T-SQL. I have 2 tables, eTasks and eStaff. The following 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 taken out, these tables never have the same exact rows each day. Some days, there will be a 1000 rows in eTask table and the next there could just be 400. Some days there will be 3 staff members in the eStaff table and the next there could be 12.

What I would like to do is to evenly distribute the tasks among the current StaffIDs when I run 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 query currently is, it'll only divide up the tasks among 4 people. Is there a way to make a CASE statement dynamic so that way there isn't just a set of static numbers?

Answer 1

This would be one way to go about assigning task to employees as Ankur mentioned above using a loop, but might not be the answer you are looking for. Hope it helps.

    IF OBJECT_ID('tempdb..#Result') IS NOT NULL
    DROP TABLE #Result

    CREATE Table #Result
        (
        Employee int,
        task int
        )


    -- number of staff
    DECLARE @Staff INT = 12 -- could use (SELECT COUNT(*) FROM (SELECT DISTINCT StaffID FROM eStaff) a) to get actual count
    -- number of tasks
    DECLARE @Task INT = 49 -- could use (SELECT COUNT(*) FROM (SELECT DISTINCT TaskID FROM eTasks) a) to get actual count

    DECLARE @Staffholder INT = 1 -- staff place holder for loop
    DECLARE @Loop INT = 1

    WHILE @Loop <= @Task
        BEGIN

        INSERT INTO #Result (Employee, task)
        SELECT @Staffholder, @Loop

        SET @Loop = @Loop + 1
        SET @Staffholder = CASE WHEN @Staffholder = @Staff THEN 1 ELSE @Staffholder + 1 END


        END

        SELECT * FROM #Result
        ORDER BY Employee