Employee Employment Date Filter

 /* Formatted on 10/3/2025 6:05:04 AM (QP5 v5.256.13226.35538) */

SELECT *

FROM (

  SELECT '101' emp_no, TO_DATE('01-JAN-2024','DD-MON-YYYY') date_of_employment, TO_DATE('10-JAN-2025','DD-MON-YYYY') date_of_leaving FROM DUAL

  UNION ALL

  SELECT '102', TO_DATE('01-FEB-2024','DD-MON-YYYY'), TO_DATE('10-JAN-2025','DD-MON-YYYY') FROM DUAL

  UNION ALL

  SELECT '103', TO_DATE('01-FEB-2025','DD-MON-YYYY'), TO_DATE('10-SEP-2025','DD-MON-YYYY') FROM DUAL

  UNION ALL

  SELECT '104', TO_DATE('01-JAN-2025','DD-MON-YYYY'), TO_DATE('10-SEP-2025','DD-MON-YYYY') FROM DUAL

  UNION ALL

  SELECT '105', TO_DATE('01-OCT-2025','DD-MON-YYYY'), TO_DATE('30-OCT-2025','DD-MON-YYYY') FROM DUAL

  UNION ALL

  SELECT '106', TO_DATE('01-FEB-2026','DD-MON-YYYY'), TO_DATE('01-MAY-2026','DD-MON-YYYY') FROM DUAL

  UNION ALL

  SELECT '107', TO_DATE('01-JAN-2025','DD-MON-YYYY'), NULL FROM DUAL

  UNION ALL

  SELECT '108', TO_DATE('01-OCT-2025','DD-MON-YYYY'), NULL FROM DUAL

  UNION ALL

  SELECT '109', TO_DATE('01-FEB-2026','DD-MON-YYYY'), NULL FROM DUAL

)

WHERE emp_no IN (NVL('&emp_no_', emp_no))

  AND TRUNC(date_of_employment) >= TO_DATE('&date_of_employment_','DD-MON-YYYY')

  AND (

        (date_of_leaving IS NULL AND date_of_employment <= TO_DATE('&date_of_leaving_','DD-MON-YYYY'))

        OR (TRUNC(date_of_leaving) <= TO_DATE('&date_of_leaving_','DD-MON-YYYY'))

      )