Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE Payrollrecords (leave_type, start_date, end_date, leave_number) AS
SELECT 'Annual Leave', DATE '2021-10-08', DATE '2021-10-08', 24042 FROM DUAL UNION ALL
SELECT 'Annual Leave', DATE '2021-11-21', DATE '2021-11-29', 24043 FROM DUAL UNION ALL
SELECT 'Annual Leave', DATE '2021-12-23', DATE '2021-12-23', 30069 FROM DUAL UNION ALL
SELECT 'Annual Leave', DATE '2021-12-29', DATE '2021-12-31', 30112 FROM DUAL UNION ALL
SELECT 'Annual Leave', DATE '2022-01-24', DATE '2022-01-24', 30189 FROM DUAL UNION ALL
SELECT 'Annual Leave', DATE '2022-12-01', DATE '2022-12-01', 12345 FROM DUAL UNION ALL
SELECT 'Annual Leave', DATE '2022-12-02', DATE '2022-12-02', 12345 FROM DUAL UNION ALL
SELECT 'Annual Leave', DATE '2021-01-01', DATE '2022-12-31', 12345 FROM DUAL;
8 rows affected

SELECT *
FROM   (
  SELECT p.*,
         MAX(CASE WHEN start_date <  DATE '2021-11-20' THEN start_date END) OVER ()
           AS max_start_date,
         COALESCE(
           MIN(CASE WHEN end_date >= DATE '2022-11-30' THEN end_date END) OVER (),
           DATE '9999-12-31' -- Or SYSDATE
         ) AS min_end_date
  FROM   Payrollrecords p
)
WHERE  start_date >= max_start_date
AND    end_date   <= min_end_date;


LEAVE_TYPE
START_DATE
END_DATE
LEAVE_NUMBER
MAX_START_DATE
MIN_END_DATE


Annual Leave
2021-10-08 00:00:00
2021-10-08 00:00:00
24042
2021-10-08 00:00:00
2022-12-01 00:00:00

Annual Leave
2021-11-21 00:00:00
2021-11-29 00:00:00
24043
2021-10-08 00:00:00
2022-12-01 00:00:00

Annual Leave
2021-12-23 00:00:00
2021-12-23 00:00:00
30069
2021-10-08 00:00:00
2022-12-01 00:00:00

Annual Leave
2021-12-29 00:00:00
2021-12-31 00:00:00
30112
2021-10-08 00:00:00
2022-12-01 00:00:00

Annual Leave
2022-01-24 00:00:00
2022-01-24 00:00:00
30189
2021-10-08 00:00:00
2022-12-01 00:00:00

Annual Leave
2022-12-01 00:00:00
2022-12-01 00:00:00
12345
2021-10-08 00:00:00
2022-12-01 00:00:00


SELECT leave_type, start_date, end_date, leave_number
FROM   (
  SELECT p.*,
         MAX(CASE WHEN start_date <  DATE '2021-11-20' THEN start_date END) OVER ()
           AS max_start_date,
         COALESCE(
           MIN(CASE WHEN end_date   >= DATE '2022-11-30' THEN end_date   END) OVER (),
           DATE '9999-12-31' -- Or SYSDATE
         ) AS min_end_date
  FROM   Payrollrecords p
)
WHERE  start_date <= min_end_date
AND    end_date   >= max_start_date;


LEAVE_TYPE
START_DATE
END_DATE
LEAVE_NUMBER


Annual Leave
2021-10-08 00:00:00
2021-10-08 00:00:00
24042

Annual Leave
2021-11-21 00:00:00
2021-11-29 00:00:00
24043

Annual Leave
2021-12-23 00:00:00
2021-12-23 00:00:00
30069

Annual Leave
2021-12-29 00:00:00
2021-12-31 00:00:00
30112

Annual Leave
2022-01-24 00:00:00
2022-01-24 00:00:00
30189

Annual Leave
2022-12-01 00:00:00
2022-12-01 00:00:00
12345

Annual Leave
2021-01-01 00:00:00
2022-12-31 00:00:00
12345

LEAVE_TYPE	START_DATE	END_DATE	LEAVE_NUMBER	MAX_START_DATE	MIN_END_DATE
Annual Leave	2021-10-08 00:00:00	2021-10-08 00:00:00	24042	2021-10-08 00:00:00	2022-12-01 00:00:00
Annual Leave	2021-11-21 00:00:00	2021-11-29 00:00:00	24043	2021-10-08 00:00:00	2022-12-01 00:00:00
Annual Leave	2021-12-23 00:00:00	2021-12-23 00:00:00	30069	2021-10-08 00:00:00	2022-12-01 00:00:00
Annual Leave	2021-12-29 00:00:00	2021-12-31 00:00:00	30112	2021-10-08 00:00:00	2022-12-01 00:00:00
Annual Leave	2022-01-24 00:00:00	2022-01-24 00:00:00	30189	2021-10-08 00:00:00	2022-12-01 00:00:00
Annual Leave	2022-12-01 00:00:00	2022-12-01 00:00:00	12345	2021-10-08 00:00:00	2022-12-01 00:00:00