I have a table which have records like this
x
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.