SQL Server Case .. When .. in Statement

SQL Server CASE .. WHEN .. IN statement

CASE AlarmEventTransactions.DeviceID should just be CASE.

You are mixing the 2 forms of the CASE expression.

IN condition within CASE statement in SQL Server

SQL Server doesn't have a Boolean data type. You could instead refactor to a nested CASE expression as below, returning 0 or 1:

SELECT * from source
WHERE
    1 =
         CASE WHEN 234 NOT IN(
            SELECT  
                user_id
            FROM
                usergroup  
            )
        THEN
            CASE WHEN SOURCE.source_id IN(
                SELECT DISTINCT
                    sl.source_id
                FROM
                sourcelevel SL

            ) THEN 1 
              ELSE 0
             END

        ELSE
            1
        END;

I think this could also be refactored without CASE, perhaps like:

SELECT *
FROM source
WHERE 234 IN(
    SELECT user_id
    FROM usergroup  
    )
    OR 
    SOURCE.source_id IN(
        SELECT sl.source_id
        FROM sourcelevel SL
    );

Using CASE Statement inside IN Clause

CASE returns a scalar value only. You can do this instead. (I am assuming, as per your example, that when @StatusID = 99, a StatusID value of 99 is not a match.)

select *
from MyTable
where (@StatusID = 99 and StatusID in (5, 11, 13))
    or (@StatusID <> 99 and StatusID = @StatusID)

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;

SQL Server Case statement for date condition

If I understand your issue correctly, you need a combination of CASE expression and windowed MIN():

SELECT 
   *,
   CASE 
      WHEN MIN(Start_Date) OVER (PARTITION BY Product_Code) = 2019 THEN 'Old'
      WHEN MIN(Start_Date) OVER (PARTITION BY Product_Code) = 2020 THEN 'Mature'
      WHEN MIN(Start_Date) OVER (PARTITION BY Product_Code) = 2021 THEN 'New'
      ELSE ''
   END AS Status
FROM (VALUES
   ('abc10', 2019, 2020, 10),
   ('abc10', 2020, 2021, 11),
   ('abc10', 2021, 2025, 12),
   ('abc11', 2020, 2021, 10),
   ('abc11', 2021, 2025, 12),
   ('abc12', 2021, 2025, 15)
) t (Product_Code, Start_Date, End_Date, Price)
ORDER BY Product_Code, Start_Date

Result:

select (case when (b.stars >= 2.0 and b.stars <= 3.0) then '2-3'
             when (b.stars >= 4.0) then '4-5'
             else 'none'
        end) as stars_group

select (case trunc(b.stars)
             when 2 then 'Two'
             when 3 then 'Three'
             else 'none'
        end) as stars_group

UPDATE Table
    SET Alias = SUBSTRING(Names, 10, 40);

UPDATE Table
   SET Alias = CASE /*****************************************************************************************/
                    WHEN Alias in('core.user','core.idea','core.award')
                    THEN Alias                  
                    /*****************************************************************************************/
                    WHEN Alias LIKE 'core.user%'  OR Alias LIKE  'core.idea%' OR Alias LIKE'core.form%' 
                    THEN STUFF(Alias, CHARINDEX('.', Alias) + 5, 0, '-') 
                    WHEN  Alias LIKE 'core.badge%'  OR Alias LIKE  'core.award%' OR Alias LIKE 'core.field%' OR Alias LIKE 'core.audit%' OR Alias LIKE 'core.event%'
                    THEN STUFF(Alias, CHARINDEX('.', Alias) + 6, 0, '-')
                    ELSE CASE len(Alias) when charindex('.',Alias) +4 then Alias
                         ELSE CASE len(Alias) when charindex('.',Alias)+ 5 then Alias
                               END
                         END
                END;