title: SQL - Using Case Statements with Dynamic tables (unknown row count)
body: <p>I'm having a bit of an issue with trying to create a stored procedure. I have 2 tables, <code>eTasks</code> and <code>eStaff</code>. The following are the columns for each table:</p>
  
  <p>eTasks</p>
  
  <pre><code>TaskID | StaffID | Title | CreateDate
  </code></pre>
  
  <p>eStaff</p>
  
  <pre><code>StaffID | Name
  </code></pre>
  
  <p>Currently, as the data stands, all tasks are assigned to StaffID '1'.  Both <code>eTasks</code> and <code>eStaff</code> 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 <code>eTask</code> table and the next there could just be 400.  Some days there will be 3 staff members in the <code>eStaff</code> table and the next there could be 12.</p>
  
  <p>What I would like to do is to evenly distribute the tasks among the current <code>StaffID</code>s when I run my stored procedure.  </p>
  
  <p>So far, this is what I have:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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?</p>