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')
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?
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.