Or Is Not Supported With Case Statement in SQL Server

OR is not supported with CASE Statement in SQL Server

That format requires you to use either:

CASE ebv.db_no 
  WHEN 22978 THEN 'WECS 9500' 
  WHEN 23218 THEN 'WECS 9500'  
  WHEN 23219 THEN 'WECS 9500' 
  ELSE 'WECS 9520' 
END as wecs_system

Otherwise, use:

CASE  
  WHEN ebv.db_no IN (22978, 23218, 23219) THEN 'WECS 9500' 
  ELSE 'WECS 9520' 
END as wecs_system

case statement for multiple values

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;

How to use a case statement when dependent on another table

Using LEFT JOIN to get corresponding PERSON_ID value from the second table:

SELECT 
t1.PERSON_ID,
t1.COUNTRY_CODE,
t1.PURCHASE_DESCRIPTION,
t1.CLICKED_ID,
t1.CLAIMED_ID,
t1.BOUGHT_ID,
t1.START_DATETIME,
CASE WHEN t1.STATUS = 'INACTIVE' THEN 'INACTIVE'
     WHEN t1.STATUS = 'ACTIVE' AND t2.PERSON_ID IS NOT NULL  THEN 'INACTIVE'
     WHEN t1.STATUS = 'ACTIVE' AND t2.PERSON_ID IS NULL      THEN 'ACTIVE'
END AS STATUS
FROM TABLE_1 AS t1
LEFT JOIN TABLE_2 AS t2
  ON t1.PERSON_ID = t2.PERSON_ID;

Output:

Sample Image

SQL UPDATE and CASE statement does not work

Please check the next query:

WITH cte_previous_rows AS (
    SELECT Date, Staff_Id, LAG(FieldX) OVER (partition by Staff_Id ORDER by [date]) as Prev_Row
FROM Sales 
) UPDATE Sales
SET FieldX = (CASE 
    WHEN Staff_id_sales < 1500 AND ClosedSale = 0 THEN 0
    WHEN Staff_id_sales = 1500 and ClosedSale = 0 THEN 5
    WHEN Staff_id_sales > 3000 and (c.Prev_Row = 1 OR c.Prev_Row = 0) THEN 2
    WHEN Staff_id_sales > 3000 and (c.Prev_Row = 2 or c.Prev_Row = 3) THEN 3 
    ELSE FieldX
END) 
FROM Sales
JOIN cte_previous_rows as c ON Sales.staff_id = c.staff_id AND Sales.Date = c.Date;

result:

+============+==========+================+============+========+
| Date       | Staff_Id | Staff_id_sales | ClosedSale | FieldX |
+============+==========+================+============+========+
| 2000-01-01 | 1        | 500            | 0          | 0      |
+------------+----------+----------------+------------+--------+
| 2001-01-01 | 2        | 200            | 0          | 0      |
+------------+----------+----------------+------------+--------+
| 2001-02-26 | 3        | 500            | 0          | 0      |
+------------+----------+----------------+------------+--------+
| 2001-01-25 | 4        | 1500           | 0          | 5      |
+------------+----------+----------------+------------+--------+
| 2001-03-25 | 4        | 1500           | 0          | 5      |
+------------+----------+----------------+------------+--------+
| 2001-03-25 | 5        | 2500           | 0          | 1      |
+------------+----------+----------------+------------+--------+
| 2001-04-25 | 5        | 3000           | 0          | 1      |
+------------+----------+----------------+------------+--------+
| 2001-05-25 | 5        | 3500           | 0          | 2      |
+------------+----------+----------------+------------+--------+
| 2001-05-26 | 5        | 4000           | 0          | 3      |
+------------+----------+----------------+------------+--------+
| 2001-05-27 | 5        | 5000           | 0          | 3      |
+------------+----------+----------------+------------+--------+
| 2001-05-28 | 5        | 7500           | 0          | 3      |
+------------+----------+----------------+------------+--------+

Concat in not working inside case statement

The problem is that month is an integer, whereas the result from concat() is a string. So. case is trying to cast the string back into an integer. You could force the integer into a string by using cast, but there are better ways to do this.

Instead, just use the FORMAT function:

select
     format(month, '00') as month_new
   , month
from viivscaazure.F_SALES_DETAIL

Skip/Ignore a condition in CASE statement in SQL

If you want the variable you are creating with CASE expression to be NULL then use the appropriate null or missing value in the CASE expression.

Perhaps you mean you want to create the new colB variable to have the existing colB variable's value when it is not P or Q?

select colA
     , case
         when colB = 'P' then colC 
         when colB = 'Q' then colD
         else colB
       end as new_colB
from table_A

If you don't want the observations with COLB='R' in the results then exclude those using WHERE.

select colA
     , case
         when colB = 'P' then colC 
         when colB = 'Q' then colD
         else colB
       end as new_colB
from table_A
where colB ne 'R'

If you are actually using SAS then skip the SQL completely and just write SAS code to do whatever you want. Then you could actually have statements that are executed conditionally.

data want;
  set mylib.table_A;
  if colB='P' then do;
     * some other data step statements ;
  end;
run;

Order by calculated column with alias inside case expression

So while you can use a calculated column in your ORDER BY clause (but not in other clauses such as GROUP BY), you cannot then apply further calculations or conditions - it must be used exactly as created.

There are a whole bunch of ways to solve this problem. Which approach you use will come down to some combination of:

Which option is clearer to you as the developer
Which option performs better
Which option fits into your existing query better

Option 1: Repeat the logic

I don't recommend this option because it violates the DRY principle thereby making it harder to maintain and easier to make mistakes.

select top 10
    S.Id as EntityId
    , S.EnglishName as EntityEnglishName
    , S.[Name] as EntityNativeName
    , case
          when @Mode = 0 then 0
          when @Mode = 1 then 1
          when @Mode = 2 then 2
      end as ActiveStudents
from V_SchoolMinimized as S
order by
    case when @Sort is null then S.Id end
    , case when @Sort = 'engname' then
        case
            when @Mode = 0 then 0
            when @Mode = 1 then 1
            when @Mode = 2 then 2
        end
    end;

The rest of the options are sub-query variations the choice of which comes down to the comments provided as the start.

Option 2: Use a derived table sub-query

select top 10
    S.Id as EntityId
    , S.EnglishName as EntityEnglishName
    , S.[Name] as EntityNativeName
    , S.ActiveStudents
from (
    select *
        , case
              when @Mode = 0 then 0
              when @Mode = 1 then 1
              when @Mode = 2 then 2
          end as ActiveStudents
    from V_SchoolMinimized
) as S
order by
    case when @Sort is null then S.Id end
    , case when @Sort = 'engname' then S.ActiveStudents end;

Option 3: Use a CTE (Common Table Expression)

with cte as (
    select *
        , case
              when @Mode = 0 then 0
              when @Mode = 1 then 1
              when @Mode = 2 then 2
          end as ActiveStudents
    from V_SchoolMinimized
)
select top 10
    S.Id as EntityId
    , S.EnglishName as EntityEnglishName
    , S.[Name] as EntityNativeName
    , S.ActiveStudents
from cte
order by
    case when @Sort is null then S.Id end
    , case when @Sort = 'engname' then S.ActiveStudents end;

Option 4: Use CROSS APPLY

select top 10
    S.Id as EntityId
    , S.EnglishName as EntityEnglishName
    , S.[Name] as EntityNativeName
    , A.Students
from V_SchoolMinimized as S
cross apply (
    values (
        case
            when @Mode = 0 then 0
            when @Mode = 1 then 1
            when @Mode = 2 then 2
            end
    )
) as A (Students)
order by
    case when @Sort is null then S.Id end
    , case when @Sort = 'engname' then A.Students end;

Note: I suggest keeping your table aliases nice and short, 1-2 characters where possible, occasionally 3.

Or Is Not Supported With Case Statement in SQL Server