By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE complaint (REFNO, COMNO, CDATE, MESSAGE, STATUS) AS
SELECT '12345', 12345, DATE '2022-01-05', 'CUSTOMER ISSUE REPORTED', 'OPEN' FROM DUAL UNION ALL
SELECT '12345', 12345, DATE '2022-01-07', 'WAITING FOR THE CUSTOMER RESPONCE', 'IN PROGRESS' FROM DUAL UNION ALL
SELECT '12345', 12345, DATE '2022-01-09', 'ISSUE RESOLVED', 'CLOSED' FROM DUAL UNION ALL
SELECT '12345', 56789, DATE '2022-01-14', 'CUSTOMER ISSUE REPORTED', 'OPEN' FROM DUAL UNION ALL
SELECT '12345', 56789, DATE '2022-01-23', 'ISSUE RESOLVED', 'CLOSED' FROM DUAL UNION ALL
SELECT '12345', 85642, DATE '2022-01-03', 'CUSTOMER ISSUE REPORTED', 'OPEN' FROM DUAL UNION ALL
SELECT '12345', 78632, DATE '2022-01-30', 'CUSTOMER ISSUE REPORTED', 'OPEN' FROM DUAL UNION ALL
SELECT '12345', 78632, DATE '2022-01-31', 'WAITING FOR THE CUSTOMER RESPONCE', 'IN PROGRESS' FROM DUAL
8 rows affected
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SELECT COMNO,
MIN(CDATE) AS COMPLAINT_CREATION_DATE,
MAX(CDATE) AS COMPLAINT_CLOSURE_DATE,
MAX(MESSAGE) KEEP (DENSE_RANK LAST ORDER BY CDATE) AS message,
MAX(STATUS) KEEP (DENSE_RANK LAST ORDER BY CDATE) AS status
FROM COMPLAINT
WHERE CDATE >= DATE '2022-01-01'
AND CDATE < DATE '2022-02-01'
AND REFNO='12345'
GROUP BY COMNO
| COMNO | COMPLAINT_CREATION_DATE | COMPLAINT_CLOSURE_DATE | MESSAGE | STATUS |
|---|---|---|---|---|
| 12345 | 2022-01-05 00:00:00 | 2022-01-09 00:00:00 | ISSUE RESOLVED | CLOSED |
| 56789 | 2022-01-14 00:00:00 | 2022-01-23 00:00:00 | ISSUE RESOLVED | CLOSED |
| 78632 | 2022-01-30 00:00:00 | 2022-01-31 00:00:00 | WAITING FOR THE CUSTOMER RESPONCE | IN PROGRESS |
| 85642 | 2022-01-03 00:00:00 | 2022-01-03 00:00:00 | CUSTOMER ISSUE REPORTED | OPEN |