Where am I going wrong?

I was asked to write a query to get the following results:

EMPLID S_EMPL_STATUS_CD TE_STATUS
123456 ACT IN
789012 IN ACT

I am not sure now to get two opposite statuses to sync up so here is what I have written so far (knowing that the where clause is the problem. Can someone point out what I am doing wrong/missing?

SELECT A.EMPL_ID, A.S_EMPL_STATUS_CD, CASE WHEN B.ACTIVE_FL ='Y' THEN 'ACT' ELSE 'IN' END AS TE_STATUS
FROM DB.EMPL A
JOIN DATABASE.DB.EMPL B ON A.EMPL_ID = B.EMPL_ID COLLATE DATABASE_DEFAULT
WHERE A.S_EMPL_STATUS_CD IN (A.S_EMPL_STATUS_CD ='ACT' AND B.ACTIVE_FL = 'N' OR A.S_EMPL_STATUS_CD != 'ACT' AND B.ACTIVE_FL = 'Y')

Many thanks!

Try this,

SELECT A.EMPL_ID, A.S_EMPL_STATUS_CD,
 CASE WHEN B.ACTIVE_FL ='Y' THEN 'ACT' ELSE 'IN' END AS TE_STATUS
FROM DB.EMPL A
JOIN DATABASE.DB.EMPL B ON A.EMPL_ID = B.EMPL_ID COLLATE DATABASE_DEFAULT

Where A.S_EMPL_STATUS_CD ='ACT' 
OR A.S_EMPL_STATUS_CD <>'ACT' 
and B.ACTIVE_FL in('N','Y')

You cant mix 2 different columns the "IN" clause, and why are you having status as 'ACT' and Not 'ACT'? is there more than ACT in the column? the operand for not equal is <> 'ACT'

Hi Pasi - Thank you for your reply. Unfortunately, the proposed did not return a result set where the employee has one status in the S_EMPL_STATUS_CD and a different status in TE_STATUS and vice versa. That is the ask. Any other suggestions?

Can you show your result set? and columns associated? does B.ACTIVE_FL column has Y or N? If so

just change CASE:
CASE WHEN B.ACTIVE_FL ='Y' THEN 'ACT'
When B.ACTIVE_FL ='N' Then 'IN' END AS TE_STATUS

Where B.ACTIVE_FL in('N','Y')

Result set has all the employees listed with their statuses in EMPL_STATUS_CD and TE_STATUS matching in those columns. The ask is to see only the employees where their status don't match.

Please post sample data in consumable format(create table or declare table variable with inserts of sample data).

Hi Joe - table with data as follows:

CREATE TABLE EMPL (
EMPL_ID, VARCHAR (12), NOT NULL
S_EMPL_STATUS_CD, VARCHAR (3), NOT NULL)

CREATE TABLE EMPL (
EMPL_ID (PK, VARCHAR (20), NOT NULL
ACTIVE_FL VARCHAR (1)

INSERT INTO DB.EMPL VALUES (123456, ACT)
INSERT INTO DB.EMPL VALUES (789012, IN)

INSERT INTO DB.EMPL VALUES (123456, N)
INSERT INTO DB.EMPL VALUES (789012, Y)

Does your script run on your SQL Server? It doesn't on mine.