How to get the latest status from the table while using max(date)

Question

Here is one table name called.. COMPLAINTS :-

COMNO       CDATE           MESSAGE                             STATUS
----------------------------------------------------------------------
12345       05-JAN-22       CUSTOMER ISSUE REPORTED             OPEN        
12345       07-JAN-22       WAITING FOR THE CUSTOMER RESPONCE   IN PROGRESS
12345       09-JAN-22       ISSUE RESOLVED                      CLOSED
56789       14-JAN-22       CUSTOMER ISSUE REPORTED             OPEN
56789       23-JAN-22       ISSUE RESOLVED                      CLOSED
85642       03-JAN-22       CUSTOMER ISSUE REPORTED             OPEN
78632       30-JAN-22       CUSTOMER ISSUE REPORTED             OPEN
78632       31-JAN-22       WAITING FOR THE CUSTOMER RESPONCE   IN PROGRESS

Here is my oracle query -

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') 
   AND REFNO='12345'
 GROUP BY COMNO 

which gives me the result of REFNO='12345'

COMNO       COMPLAINT_CREATION_DATE   COMPLAINT_CLOSURE_DATE
--------------------------------------------------------
12345       05-JAN-22                 09-JAN-22

In this query i want to add the last status and message of all complaints

COMNO + COMPLAINT_CREATION_DATE + COMPLAINT_CLOSURE_DATE + MESSAGE + STATUS
-------------------------------------------------------------------
12345       05-JAN-22       09-JAN-22       ISSUE RESOLVED                      CLOSED
56789       14-JAN-22       23-JAN-22       ISSUE RESOLVED                      CLOSED
85642       03-JAN-22       03-JAN-22       CUSTOMER ISSUE REPORTED             OPEN
78632       30-JAN-22       31-JAN-22       WAITING FOR THE CUSTOMER RESPONCE   IN PROGRESS

Can anyone please help me on that.

Answer 1

Don't use a self-join, use KEEP (DENSE_RANK LAST ORDER BY CDATE):

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 

Or analytic functions:

SELECT comno,
       COMPLAINT_CREATION_DATE,
       cdate AS COMPLAINT_CLOSURE_DATE,
       message,
       status
FROM   (
  SELECT c.*,
         MIN(CDATE) OVER (PARTITION BY comno) AS COMPLAINT_CREATION_DATE,
         ROW_NUMBER() OVER (PARTITION BY comno ORDER BY cdate DESC) AS rn
    FROM COMPLAINT c
   WHERE CDATE >= DATE '2022-01-01'
     AND CDATE <  DATE '2022-02-01' 
     AND REFNO='12345'
)
WHERE  rn = 1

Which, for the sample data:

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

Both output:

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

db<>fiddle here

Answer 2

You can remove the filter condition form your query and join it again to the table -

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 COMPLAINTS C ON CD.COMNO = C.COMNO
                   AND CD.COMPLAINT_CLOSURE_DATE = C.CDATE;

Demo.