SurajS SurajS - 12 days ago 7
SQL Question

SQL Server : create batch of Employees and prevent one emp going to multiple batches

My table contains data about

Employee
. However it is a temporary table and
EmployeeID
here isn't the primary key. The table may contain a given
EmployeeID
multiple times.

Now, I have to select batch of records of
batchSize
, let's consider 200 for now. I'll send these batches to multiple threads.

I have written this query:

WITH SingleBatch AS
(
SELECT
*,
ROW_NUMBER() OVER(ORDER BY EmployeeId) AS RowNumber
FROM
TemperoryTable
)
SELECT *
FROM SingleBatch
WHERE RowNumber BETWEEN 1 AND 200;


the result might be:

EmployeeID EffectiveDate
1 123 01/01/2016
2 541 01/01/2016
------------------------
------------------------
200 978 18/06/2015


for one batch.
This works fine and row numbers change with thread number.

Now suppose, second batch starts with EmployeeId
978
. Then this employee will be in first batch as well as second batch. That is, same employee is being sent to multiple threads and may possibly cause conflict.
Although the scenario is very rare, I must avoid this.

What could be the possible solution here?

Answer

Sorry I don't get it before, you wish same empolyee can be gotten together? but the total return rows count possible is not fix number. May this is helpful for you.

    ;WITH t(RowNumber,EmployeeId,other)AS
    (
        SELECT 1,'a','1' UNION ALL
        SELECT 2,'a','12' UNION ALL
       SELECT 3,'a','13' UNION ALL
       SELECT 4,'b','21' UNION ALL
       SELECT 5,'d','41' UNION ALL
       SELECT 6,'c','31' UNION ALL
       SELECT 7,'c','32' 

    )
    SELECT *,DENSE_RANK()OVER(ORDER BY EmployeeId) AS FilterID,RANK()OVER(ORDER BY EmployeeId) RowsCount FROM t
RowNumber   EmployeeId other FilterID             RowsCount
----------- ---------- ----- -------------------- --------------------
2           a          12    1                    1
3           a          13    1                    1
1           a          1     1                    1
4           b          21    2                    4
6           c          31    3                    5
7           c          32    3                    5
5           d          41    4                    7

Same employeeid has same FilterID, and the RowsCount to control return rows count. You should get data by RowsCount but rownumber.

For example: Actual return 6 lines when the RowsCount between 1 and 5. because the employeeID c have two lines.