SQL DB2: I need to filter results based on query results

Question

I would need to get the id's for given input employee_id's and given input date:

condition: I need to query for employee's who are enrolled on the given date or less then given date in the input.

constrain: the employee shouldn't have 'disEnrolled' status on or before the given input date.

for example: if the employee_id's are 32456 and 32458 and given input date is 2021-01-01, then expected result is id value 3 with employee_Id - 32458. The employee_Id (32456) is not qualified because he has disenrolled on or before given input date.

I have a table with below columns:

id	date	status	employee_Id
1	2021-01-01	enrolled	32456
2	2021-01-01	disEnrolled	32456
3	2020-01-01	enrolled	32458

below is what I have tried:

select a.ID 
  from  EMPLOYEE  a
 where (a.employee_id='34526' 
        or a.employee_id = '32547')
   and (
        (a.status= 'enrolled' 
         and {d '2021-10-10'} >= p.date
        ) 
        or (a.status NOT IN 'disEnrolled' 
             and {d '2021-10-10'} <= a.date
           )
       );

Answer 1

You can use a simple EXISTS clause to achieve the desired result -

SELECT Id
  FROM EMPLOYEE E
 WHERE status = 'enrolled'
   AND NOT EXISTS (SELECT NULL
                     FROM EMPLOYEE E2
                    WHERE E.employee_Id = E2.employee_Id
                      AND E2.status = 'disEnrolled')
   AND date <= DATE '2021-01-01'
   AND Employee_ids IN (empId_1, empId_2, .....);   -- Youe Employee ID list

But this query will produce 2 records as 32457 and 32458 instead of only 32458.

Answer 2

WITH TAB (id, date, status, employee_Id) AS
(
VALUES
  (1, '2021-01-01', 'enrolled', 32456)
, (2, '2021-01-01', 'disEnrolled', 32456)
, (4, '2020-01-01', 'enrolled', 32458)
)
SELECT DISTINCT employee_Id
FROM TAB A
WHERE status = 'enrolled'
AND NOT EXISTS
(
SELECT 1
FROM TAB B
WHERE B.employee_ID = A.employee_Id 
AND B.status = 'disEnrolled' 
AND B.date <= '2021-01-01'
)