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



  • 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'} 


  • 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


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2