How to divide SQL results into equal parts using where clause and/or case statement?-CodePudding

Context: I am preparing this dataset to be used for an SSRS report, my goal is to split the list into about multiple parts (equally) for employees to receive. It will be used as a "task list" of sorts.

I have tried two different ways, both of which are causing issues.

Attempt #1 - Error is 'incorrect syntax':

SELECT
CASE @EmployeeName
WHEN 'Jane Doe' THEN (SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS rn FROM #TaskList) WHERE rn % 2 = 0)
WHEN 'John Doe' THEN (SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS rn FROM #TaskList) WHERE rn % 2 = 1)
ELSE 'test test'
END

Attempt #2 - Error is 'incorrect syntax':

SELECT * FROM ( 
SELECT *,
ROW_NUMBER() OVER (ORDER BY ID) AS rn 
FROM
#TaskList
) T1
WHERE
CASE @EmployeeName
WHEN 'Jane Doe' THEN rn % 2 = 1
WHEN 'John Doe' THEN rn % 2 = 0
END

Is there any way to accomplish this using my method or similar method?

CodePudding user response：

In both of your attempts it seems to me you have WHEN and THEN values/statements reversed.

Something like this should work...

SELECT *
FROM   (SELECT *, ROW_NUMBER () OVER (ORDER BY ID) AS rn FROM #TaskList) T1
WHERE  CASE
           WHEN rn % 2 = 1 THEN
               'Jane Doe'
           WHEN rn % 2 = 0 THEN
               'John Doe'
       END = @EmployeeName;

Also, if your ID is sequential you could just skip the whole ROW_NUMBER() logic and replace rn with ID like this...

SELECT *
FROM   #TaskList
WHERE  CASE
           WHEN ID % 2 = 1 THEN
               'Jane Doe'
           WHEN ID % 2 = 0 THEN
               'John Doe'
       END = @EmployeeName;