OR in CASE WHEN, which in itself in a WHERE clause

Question

The first post is here, but I have been a lurker for many years. Here:

I have this query that has a CASE WHEN clause in WHERE. However, I need to find two possible states within CASE WHEN.

The code is as follows:

SELECT        
[...]

FROM            
[...]

WHERE        
(Admissions.Session = @Session) AND 

(Admissions.Remark IN 
CASE (RIGHT(@Session, 1)) 
    WHEN 1 THEN ('HY', 'HN')
    WHEN 3 THEN ('AY', 'AN')
END)

Basically, I'm trying to get all the lines where Admissions.Remark is either “HY” or “HN” when the last digit of the session parameter is “1”. If the session parameter ends in "3", I would like to get comments that are "AY" or "AN"

I also tried something like this:

(Admissions.Remark = CASE (RIGHT(@Session, 1)) 
    WHEN 1 THEN 'HY' 
    WHEN 1 THEN 'HN' 
    WHEN 3 THEN 'AY' 
    WHEN 3 THEN 'AN' 
END)

... but no one worked. The latter only returned the lines where the remark “HY” was given, ignoring “HN”.

So I'm looking for something like this:

(Admissions.Remark = CASE (RIGHT(@Session, 1)) 
    WHEN 1 THEN 'HY' OR 'HN' 
    WHEN 3 THEN 'AY' OR 'AN'
END)

? , , - . !

Answer 1

:

SELECT ...
FROM ...
WHERE 
   (CASE WHEN Admissions.Remark IN ('HY', 'HN') THEN 1 
         WHEN Admissions.Remark IN ('AY', 'AN') THEN 3
   END) = RIGHT(@Session, 1) 

Answer 2

, ,

Admissions.Remark = CASE 
    WHEN RIGHT(@Session, 1) = 1 AND ('HY', 'HN')
    WHEN RIGHT(@Session, 1) = 3 AND ('AY', 'AN')
END

Answer 3

You can also try the following instructions

SELECT ...
FROM ...
WHERE 
   ((Admissions.Remark  ='HY' OR Admission.Remark = 'HN') AND  RIGHT(@Session, 1) = 1)
    OR ((Admissions.Remark ='AY'OR Admission.Remark = 'AN') AND RIGHT(@Session, 1) = 3)