By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH COMPLAINT AS (SELECT 12345 AS COMNO, '05-JAN-22' AS CDATE, 'CUSTOMER ISSUE REPORTED' AS MESSAGE, 'OPEN' AS STATUS FROM DUAL UNION ALL
SELECT 12345, '07-JAN-22', 'WAITING FOR THE CUSTOMER RESPONCE', 'IN PROGRESS' FROM DUAL UNION ALL
SELECT 12345, '09-JAN-22', 'ISSUE RESOLVED', 'CLOSED' FROM DUAL UNION ALL
SELECT 56789, '14-JAN-22', 'CUSTOMER ISSUE REPORTED', 'OPEN' FROM DUAL UNION ALL
SELECT 56789, '23-JAN-22', 'ISSUE RESOLVED', 'CLOSED' FROM DUAL UNION ALL
SELECT 85642, '03-JAN-22', 'CUSTOMER ISSUE REPORTED', 'OPEN' FROM DUAL UNION ALL
SELECT 78632, '30-JAN-22', 'CUSTOMER ISSUE REPORTED', 'OPEN' FROM DUAL UNION ALL
SELECT 78632, '31-JAN-22', 'WAITING FOR THE CUSTOMER RESPONCE', 'IN PROGRESS' FROM DUAL)
SELECT C.COMNO,
CD.COMPLAINT_CREATION_DATE,
CD.COMPLAINT_CLOSURE_DATE,
C.MESSAGE,
C.STATUS
FROM (SELECT COMNO,
MIN(CDATE) AS COMPLAINT_CREATION_DATE,
MAX(CDATE) AS COMPLAINT_CLOSURE_DATE
FROM COMPLAINT
WHERE CDATE BETWEEN TO_DATE('01/Jan/2022 00:00:00','dd/Mon/yyyy hh24:mi:ss')
AND TO_DATE('31/Jan/2022 23:59:59','dd/Mon/yyyy hh24:mi:ss')
GROUP BY COMNO) CD
JOIN COMPLAINT C ON CD.COMNO = C.COMNO
AND CD.COMPLAINT_CLOSURE_DATE = C.CDATE;
| COMNO | COMPLAINT_CREATION_DATE | COMPLAINT_CLOSURE_DATE | MESSAGE | STATUS |
|---|---|---|---|---|
| 78632 | 30-JAN-22 | 31-JAN-22 | WAITING FOR THE CUSTOMER RESPONCE | IN PROGRESS |
| 85642 | 03-JAN-22 | 03-JAN-22 | CUSTOMER ISSUE REPORTED | OPEN |
| 56789 | 14-JAN-22 | 23-JAN-22 | ISSUE RESOLVED | CLOSED |
| 12345 | 05-JAN-22 | 09-JAN-22 | ISSUE RESOLVED | CLOSED |