Oracle 18c

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 datatable
(
   "DT_ID"                NUMBER (10, 0),
   "ASSIGNED"             NUMBER (1, 0) DEFAULT 0,
   "ASSIGNED_DATE_TIME"   DATE,
   "ASSIGNED_BY"          VARCHAR2 (7 BYTE),
   "ACCEPTED"             NUMBER (1, 0) DEFAULT 0,
   "ACCEPTED_DATE_TIME"   DATE,
   "ACCEPTED_BY"          VARCHAR2 (7 BYTE),
   "REJECTED"             NUMBER (1, 0) DEFAULT 0,
   "REJECTED_DATE_TIME"   DATE,
   "REJECTED_BY"          VARCHAR2 (7 BYTE)
);
INSERT INTO dataTable
SELECT 1, 0, DATE '2023-01-01', 'Alice', 1, DATE '2023-02-01', 'Betty', 2, DATE '2023-03-01', 'Carol' FROM DUAL
1 rows affected

CREATE TABLE newTable(
  id          NUMBER(10,0),
  action_user VARCHAR2(7 BYTE),
  action_date DATE
);
INSERT INTO newTable(id, action_user, action_date )
SELECT DT_ID,
       CASE GREATEST(assigned_date_time, accepted_date_time, rejected_date_time)
       WHEN rejected_date_time
       THEN rejected_by
       WHEN accepted_date_time
       THEN accepted_by
       WHEN assigned_date_time
       THEN assigned_by
       END,
       GREATEST(assigned_date_time, accepted_date_time, rejected_date_time)
FROM   datatable;
1 rows affected

SELECT * FROM newTable;


ID
ACTION_USER
ACTION_DATE


1
Carol
2023-03-01 00:00:00