I have a table which have records like this
ID DATEADD STATUS 'A0011' '04/01/2018 11:58:31' 'C' 'A0011' '31/05/2019 10:02:36' 'N' 'B0022' '04/01/2018 11:58:31' 'N' 'B0022' '31/05/2019 10:02:36' 'N' 'B0022' '30/04/2020 19:44:36' 'C' 'C0033' '04/01/2018 11:58:31' 'N' 'C0033' '30/05/2019 06:02:36' 'C' 'C0033' '29/04/2020 05:44:36' 'C'
I’m trying to get the Max Date for each ID which have STATUS = ‘N’. If I get MAX DATE and STATUS = ‘C’ then I don’t want that record.
Output :
ID DATEADD STATUS 'A0011' '31/05/2019 10:02:36' 'N'
SCRIPT :
SELECT I.* FROM INVOICE I INNER JOIN ( Select ID,MAX(DATEADD)DATEADD,STATUS FROM INVOICE WHERE STATUS = 'N' GROUP BY ID,STATUS) O ON I.ID = O.ID AND O.DATEADD = I.DATEADD
But I’m not able to get desired output.
Advertisement
Answer
You can use NOT EXISTS
:
SELECT i1.* FROM INVOICE i1 WHERE i1.STATUS = 'N' AND NOT EXISTS ( SELECT 1 FROM INVOICE i2 WHERE i2.ID = i1.ID AND STR_TO_DATE(i2.DATEADD, '%d/%m/%Y %H:%i:%s') > STR_TO_DATE(i1.DATEADD, '%d/%m/%Y %H:%i:%s') );
If the column’s DATEADD
data type is DATETIME
or TIMESTAMP
the last condition would be simpler:
...AND i2.DATEADD > i1.DATEADD
See the demo.