Oracle 21c

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

select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
0


CREATE TABLE Payrollrecords  (
  leave_type VARCHAR2(50),
  start_date DATE,
  end_date DATE,
  leave_number NUMBER
);
BEGIN
  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2021-10-08', DATE '2021-10-08', 24042);

  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2021-11-21', DATE '2021-11-29', 24043);

  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2021-12-23', DATE '2021-12-23', 30069);

  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2021-12-23', NULL, 99999);

  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2021-12-29', DATE '2021-12-31', 30112);

  INSERT INTO Payrollrecords  (leave_type, start_date, end_date, leave_number)
  VALUES ('Annual Leave', DATE '2022-01-24', DATE '2022-01-24', 30189);
END;
/
1 rows affected

select * 
from Payrollrecords


LEAVE_TYPE
START_DATE
END_DATE
LEAVE_NUMBER


Annual Leave
08-OCT-21
08-OCT-21
24042

Annual Leave
21-NOV-21
29-NOV-21
24043

Annual Leave
23-DEC-21
23-DEC-21
30069

Annual Leave
23-DEC-21
null
99999

Annual Leave
29-DEC-21
31-DEC-21
30112

Annual Leave
24-JAN-22
24-JAN-22
30189


select * 
from Payrollrecords
where start_date <= TO_DATE('2022-11-30','YYYY-MM-DD') --range_end
and (end_date >= TO_DATE('2021-11-20','YYYY-MM-DD') --range_start
    or end_date IS NULL
    )


LEAVE_TYPE
START_DATE
END_DATE
LEAVE_NUMBER


Annual Leave
21-NOV-21
29-NOV-21
24043

Annual Leave
23-DEC-21
23-DEC-21
30069

Annual Leave
23-DEC-21
null
99999

Annual Leave
29-DEC-21
31-DEC-21
30112

Annual Leave
24-JAN-22
24-JAN-22
30189

LEAVE_TYPE	START_DATE	END_DATE	LEAVE_NUMBER
Annual Leave	08-OCT-21	08-OCT-21	24042
Annual Leave	21-NOV-21	29-NOV-21	24043
Annual Leave	23-DEC-21	23-DEC-21	30069
Annual Leave	23-DEC-21	null	99999
Annual Leave	29-DEC-21	31-DEC-21	30112
Annual Leave	24-JAN-22	24-JAN-22	30189