Oracle 23c

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 table_name (ID, NAME, START_DATE, END_DATE, CREATE_DATE) AS
SELECT 100, 'Apple', DATE '2023-11-01', DATE '9999-12-31', DATE '2023-11-01' FROM DUAL UNION ALL
SELECT 100, 'Anar',  DATE '2023-10-10', DATE '9999-12-31', DATE '2023-09-12' FROM DUAL UNION ALL
SELECT 100, 'Anar',  DATE '2023-09-04', DATE '2023-10-10', DATE '2023-09-04' FROM DUAL UNION ALL
SELECT 101, 'Ant',   DATE '2023-10-01', DATE '9999-12-31', DATE '2023-10-01' FROM DUAL UNION ALL
SELECT 101, 'Bee',   DATE '2023-04-10', DATE '9999-12-31', DATE '2023-04-12' FROM DUAL UNION ALL
SELECT 101, 'Bee',   DATE '2023-03-04', DATE '2023-04-10', DATE '2023-03-04' FROM DUAL;
6 rows affected

SELECT id,
       name,
       CASE
       WHEN end_date = DATE '9999-12-31'
       THEN create_date
       ELSE start_date
       END AS start_date,
       CASE
       WHEN end_date = DATE '9999-12-31'
       THEN LEAD(create_date, 1, end_date) OVER (PARTITION BY id ORDER BY create_date)
       ELSE end_date
       END AS end_date,
       create_date
FROM   table_name
ORDER BY id, create_date


ID
NAME
START_DATE
END_DATE
CREATE_DATE


100
Anar
2023-09-04 00:00:00
2023-10-10 00:00:00
2023-09-04 00:00:00

100
Anar
2023-09-12 00:00:00
2023-11-01 00:00:00
2023-09-12 00:00:00

100
Apple
2023-11-01 00:00:00
9999-12-31 00:00:00
2023-11-01 00:00:00

101
Bee
2023-03-04 00:00:00
2023-04-10 00:00:00
2023-03-04 00:00:00

101
Bee
2023-04-12 00:00:00
2023-10-01 00:00:00
2023-04-12 00:00:00

101
Ant
2023-10-01 00:00:00
9999-12-31 00:00:00
2023-10-01 00:00:00

ID	NAME	START_DATE	END_DATE	CREATE_DATE
100	Anar	2023-09-04 00:00:00	2023-10-10 00:00:00	2023-09-04 00:00:00
100	Anar	2023-09-12 00:00:00	2023-11-01 00:00:00	2023-09-12 00:00:00
100	Apple	2023-11-01 00:00:00	9999-12-31 00:00:00	2023-11-01 00:00:00
101	Bee	2023-03-04 00:00:00	2023-04-10 00:00:00	2023-03-04 00:00:00
101	Bee	2023-04-12 00:00:00	2023-10-01 00:00:00	2023-04-12 00:00:00
101	Ant	2023-10-01 00:00:00	9999-12-31 00:00:00	2023-10-01 00:00:00