case statement for multiple values-CodePudding

I need to get the status as Both when they are working on both the projects case statement is not working for Status both

ID  Dept    Project Opted Status
123 1234    150       2
123 1234    151       2
456 4567    150       1
456 4567    151       2
789 7890    150       2
789 7890    151       1
101 1010    150       1 
101 1010    151       2

The Output should have the status , ex the 123 id has been opted out for both the projects

the case statements should get the output as below based on the above table

ID  Opted in    Opt out
123     NA        both
456    150_y      151_y
789    151_y      150_y
101    150_y      151_y

SELECT A.ID ,
CASE 
WHEN project=150 AND opted status=1 THEN 'prj_150_y'
WHEN project=151 AND opted status=1 THEN 'prj_151_Y'
WHEN project IN (150 ) AND project IN(151)AND opted status=1 THEN 'BOTH_prj_y'
OPT_IN,
CASE 
WHEN project=150 AND opted status=2 THEN 'prj_150_y'
WHEN project=151 AND opted status=2 THEN 'prj_151_y'
WHEN project IN (150 ) AND project IN(151)AND opted status=1 THEN 'BOTH_prj_y'
END AS OPT_OUT
FROM proj_dept A
JOIN
(
SELECT ID,B.*
FROM project_info
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ID,B.dept,project ORDER by optedstatus DESC)=1
WHERE A.ID IN (
7359
) AND project IN (627,152) 
ON 
A.ID=B.ID

Here is the query i have used i am unable to get the output for the case statement both for the ID 123

CodePudding user response：

You have a table with one row per ID and project. You want a result with one row per ID. This means you must aggregate your rows with GROUP BY id. In your query you don't, so project IN (150 ) AND project IN(151) refers to one row and one project only, which equals project = 150 OR project = 151 or simply project IN (150, 151). You must count matches instead (i.e. whether you have a match in the group), for which you'd use conditional aggregation (CASE inside the aggregation function).

Then, your order of boolean expressions is wrong. You opt for "project 150 only" before checking project 151. Thus you'll ever detect "both".

But yes, for two statuses in your select clause you need two CASE expresions.

SELECT 
  pd.id ,
  CASE 
    WHEN COUNT(CASE WHEN project = 150 AND opted_status = 1 THEN 1 END) > 0
     AND COUNT(CASE WHEN project = 151 AND opted_status = 1 THEN 1 END) > 0
      THEN 'BOTH_prj_y'
    WHEN COUNT(CASE WHEN project = 150 AND opted_status = 1 THEN 1 END) > 0
      THEN 'prj_150_y'
    WHEN COUNT(CASE WHEN project = 151 AND opted_status = 1 THEN 1 END) > 0
      THEN 'prj_151_y'
  END AS opt_in,
  CASE 
    WHEN COUNT(CASE WHEN project = 150 AND opted_status = 2 THEN 1 END) > 0
     AND COUNT(CASE WHEN project = 151 AND opted_status = 2 THEN 1 END) > 0
      THEN 'BOTH_prj_y'
    WHEN COUNT(CASE WHEN project = 150 AND opted_status = 2 THEN 1 END) > 0
      THEN 'prj_150_y'
    WHEN COUNT(CASE WHEN project = 151 AND opted_status = 2 THEN 1 END) > 0
      THEN 'prj_151_y'
  END AS opt_out
FROM proj_dept pd
GROUP BY pd.id
ORDER BY pd.id;

CodePudding user response：

You can do something like this:

SELECT CASE WHEN COUNT(project) == 2 THEN 'Both' ELSE MAX(project) END AS 'Project Opted' FROM A LEFT JOIN B ON ... GROUP BY [User]

This is just a simple workaround if that works for you.