SQL query not returning correct output with NOT IN-CodePudding

I have a table TAB:

Person_number       Eff_start_date      department 
--------------------------------------------------
10                  01-jun-2022         xyz
18                  15-may-2022         Instant 
19                  14-may-2022         Hudson 
20                  03-May-2022         xyz
28                  15-may-2022         Replay 
29                  14-may-2022         Bay 
30                  03-May-2022         Fifty BG

I want to create the query to fetch output:

if the eff_start_Date is < 15-may-2022 and department is not in Instant
Hudson departments should also not be included in the output.

Is this query valid ?

SELECT
    PERSON_NUMBER,
    EFF_START_DATE,
    DEPARTMENT DEPT_NAME
FROM 
    TBL
WHERE  
    TBL.DEPT_NAME NOT IN ('Hudson')
    OR (TBL.DEPT_NAME NOT IN 'Instant'
        AND TO_CHAR(EFF_START_DATE,'YYYY/MM/DD') < '2022/05/15')

If I run this query, I do not get the expected output which would be xyz, Replay, Bay, Fifty BG.

CodePudding user response：

You can use date as it is in your WHERE clause in the query. Modify your query, like so,

select PERSON_NUMBER,
EFF_START_DATE,
DEPARTMENT DEPT_NAME
FROM TBL
WHERE  TBL.DEPT_NAME <> 'Hudson'
or ( TBL.DEPT_NAME <> 'Instant'
    AND   EFF_START_DATE < TO_DATE('2022/05/15','YYYY/MM/DD')
    )

CodePudding user response：

Your Combination of logic should be something like this

SELECT
    PERSON_NUMBER,
    EFF_START_DATE,
    DEPARTMENT DEPT_NAME
FROM 
    TBL
WHERE  
    TBL.DEPT_NAME <> 'Hudson'
    AND (TBL.DEPT_NAME <> 'Instant'
        AND EFF_START_DATE < TO_DATE('2022/05/15','YYYY/MM/DD'))

Don't remove any of the brackets

CodePudding user response：

You could start by eliminating the cases whenever department is either not equals to Instant, nor Hudson. But add a CASE WHEN ... THEN expression as needing an extra condition for the case when department equals to Instant such as

SELECT *
  FROM tab
 WHERE (department NOT IN ('Instant' ,'Hudson'))
    OR CASE
         WHEN department = 'Instant' THEN
          eff_start_date
       END >= date'2022-05-15'

Demo