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

[irl]

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 4 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	2021-01-01	enrolled	32457
4	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'} 

[Marcee]

The condition "the employee shouldn't have 'disEnrolled' status on or before the given input date" needs to be done using a NOT EXISTS condition. You can't really express that with a "simple" condition in the WHERE clause:

I think the following does what you want:

select e1.*
from employee e1
where e1.employee_id in (32456, 32458)
  and e1.status = 'enrolled'
  and e1.date  e1.id
                    and e2.employee_id = e1.employee_id
                    and e2.status = 'disEnrolled'
                    and e2.date 

The first part of the WHERE condition selects those rows that fulfill the condition "employees who are enrolled on the given date or less then given date in the input"

The NOT EXISTS condition then applies the constraint "the employee shouldn't have 'disEnrolled' status on or before the given input date"

https://dbfiddle.uk/?rdbms=db2_11.1&fiddle=93bf7f8b9899bf6fa3411ab9849e2cfe