title: case statement for multiple values
body: <p>I need to get the status as Both when they are working on both the projects
  case statement is not working for Status both</p>
  <pre><code>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
  </code></pre>
  <p>The Output should have the status , ex the 123 id has been opted out for both the projects</p>
  <p>the case statements should get the output as below based on the above table</p>
  <pre><code>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
  </code></pre>
  <p>Here is the query i have used i am unable to get the output for the case statement both for the ID 123</p>