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

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.

CodePudding user response：

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.