SQL using Case When statement with MAX and Partition Over in Where Clause

Question

I have this query using a case when expression inside a MAX() aggregate function to obtain the maximum value of the case when statement.

However I need to filter also off that maximum value in the where clause. I can get the max date from the case when statement in my select clause but can't seem to understand how to filter off it in my where clause.

I know I could do a temp table and easily filter off that but I want to challenge myself and do it all in one.

This is my SQL statement - I want to get the latest status date for the ID, but I only want the one's with latest status date's >= 2022/01/01.

I know below doesn't work but I'm not sure what to do without putting this into a temp table.

SELECT 
    A1.ID, A1.STATUS, A1.DESCRIPTION,
    MAX(CASE 
            WHEN A1.DESCRIPTION IS NOT NULL
                 AND A1.STATUS = 'C'
                THEN A1.STATUS_DATE
                ELSE NULL
        END) OVER (PARTITION BY A1.ID) AS LATEST_STATUS_DATE,
FROM 
    myTable A1
WHERE 
    MAX(CASE 
            WHEN A1.DESCRIPTION IS NOT NULL
                    AND A1.STATUS = 'C'
                THEN A1.STATUS_DATE
                ELSE NULL
        END) OVER (PARTITION BY A1.ID) >= '20220101'

Answer 1

You can try GROUP BY and HAVING:

SELECT A1.ID, A1.STATUS, A1.DESCRIPTION,

    MAX(CASE WHEN A1.DESCRIPTION IS NOT NULL
                 AND A1.STATUS = 'C'
                 THEN A1.STATUS_DATE
             ELSE NULL
               END) AS LATEST_STATUS_DATE
                 
FROM myTable A1
GROUP BY A1.ID, A1.Status, A1.Description
HAVING MAX(CASE WHEN A1.DESCRIPTION IS NOT NULL
                 AND A1.STATUS = 'C'
                 THEN A1.STATUS_DATE
                 ELSE NULL
               END) >= '20220101'

Whether this actually works depends on the nature of the data, and whether Status and Description remain consistent in this table within a given ID.