SQL MAX clause for calculation of days

Question

I'm going to post some SQL rows:

    AND (CDCLI NOT IN (SELECT CDCLI FROM ANCLI07L1)
OR CDCLI IN (SELECT CDCLI FROM ANCL07L1 
WHERE Days(TIMESTAMP_FORMAT(CHAR($TODAY), 'YYYYMMDD'))
- Days(TIMESTAMP_FORMAT(CHAR(DTGR1),'YYYYMMDD')) > 30

I this portion of the SQL I would like to be able to take only the Max DTGR1 value in the WHERE statement that is when I subtract today's date with the DTGR1 field

That is, I wish I could have the WHERE like this:

    WHERE Days(TIMESTAMP_FORMAT(CHAR($TODAY), 'YYYYMMDD'))
- Days(TIMESTAMP_FORMAT(CHAR(MAX(DTGR1)),'YYYYMMDD')) > 30

who can help me ?

Answer 1

Try this query:

    WHERE(CDCLI NOT IN (SELECT CDCLI FROM ANCLI07L1)
          OR CDCLI IN (SELECT CDCLI FROM ANCL07L1 
   WHERE Days(TIMESTAMP_FORMAT(CHAR($TODAY), 'YYYYMMDD'))
       - Days(TIMESTAMP_FORMAT(CHAR((SELECT MAX(DTGR1) FROM ANCL07L1)),'YYYYMMDD')) > 30))