MAX Date Subquery SQL Server

Question

I am trying to join a max date in a subquery but I get an error. Im using SQL server

SELECT Headcount.[Snapshot Date]
, Headcount.[ID]
, Assessment.[Assessment Date]
, Assessment.[Code]

FROM Headcount INNER JOIN Assessment ON Assessment.[ID] = Headcount.[ID]
WHERE Assessment.[Assessment Date] = 
                 (SELECT MAX(CAST(Assessment.[Assessment Date] as datetime)) 
                  FROM Assessmnet AS Z 
                  WHERE Z.[ID] = Assessment.[ID] 
                       AND Assessment.[Assessment Date] <= Headcount.[Snapshot Date]
                 )

But I get an error:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Answer 1

You forgot about the alias Z that you specify in your subquery. If you use Assessment in your subquery, then he considers the Assessment from the outside query because Assessment has name Z in your subquery.

SELECT Headcount.[Snapshot Date]
, Headcount.[ID]
, Assessment.[Assessment Date]
, Assessment.[Code]

FROM Headcount INNER JOIN Assessment ON Assessment.[ID] = Headcount.[ID]
WHERE Assessment.[Assessment Date] = 
                 (SELECT MAX(CAST(Z.[Assessment Date] as datetime)) 
                  FROM Assessment AS Z 
                  WHERE Z.[ID] = Assessment.[ID] 
                       AND Z.[Assessment Date] <= Headcount.[Snapshot Date]
                 )

Answer 2

The issue is within the sub query on the where condition, try something like this:

SELECT
    Headcount.[Snapshot Date],
    Headcount.[ID],
    Assessment.[Assessment Date],
    Assessment.[Code]
FROM Headcount
INNER JOIN Assessment ON Assessment.[ID] = Headcount.[ID]
WHERE Assessment.[Assessment Date] =
(
    SELECT
        MAX(CAST(Assessment.[Assessment Date] AS DATETIME))
    FROM Assessmnet AS Z
    GROUP BY
        Z.ID
    HAVING Z.[ID] = Assessment.[ID]
           AND Assessment.[Assessment Date] <= Headcount.[Snapshot Date]
);

Answer 3

You should write MAX(CAST(Z.[Assessment Date] as datetime)) instead of MAX(CAST(Assessment.[Assessment Date] as datetime)). Otherwise, you're aggregating across the wrong table.

The following query might work (untested!):

SELECT Headcount.[Snapshot Date]
, Headcount.[ID]
, Assessment.[Assessment Date]
, Assessment.[Code]
FROM Headcount
INNER JOIN Assessment ON Assessment.[ID] = Headcount.[ID]
WHERE Assessment.[Assessment Date] =
                 (SELECT MAX(CAST(Z.[Assessment Date] as datetime))
                  FROM Assessment AS Z
                  WHERE Z.[ID] = Assessment.[ID] 
                    AND Z.[Assessment Date] <= Headcount.[Snapshot Date])

You can also rewrite it to use ROW_NUMBER to pick the most recent row for each assessment:

SELECT [Snapshot Date], [ID], [Assessment Date], [Code]
FROM (SELECT [Snapshot Date], [ID], [Assessment Date], [Code],
        ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [Assessment Date] DESC) rn
      FROM Headcount JOIN Assessment USING ([ID])
      WHERE [Assessment Date] <= [Snapshot Date]) t
WHERE rn = 1