An aggregate may not appear in the WHERE clause

Question

I'm attempting to execute the following query to return a data set where the Updated Date is the most recent. I'm thinking my joins are causing the issue, but I'm not sure, and have not made any progress.

   SELECT wd.SAPID, rd.RequestID, vd.VersionTableID,rd.ProjectReference,contactinfo.FirstName,contactinfo.LastName,
                 wd.ActualEndDate, wd.IsIndependentContractor,we.AssignmentStartDate, vd.VersionNo, we.CreatedDate, we.UpdatedDate
   FROM RequestDetails rd
   INNER JOIN  WorkerDetails wd ON  rd.RequestTableID = wd.RequestTableID
   INNER JOIN WorkerExtension we ON we.WorkerID=wd.WorkerID
   INNER JOIN dbo.Address_Info addressinfo ON addressinfo.WorkerID = wd.WorkerID
   INNER JOIN dbo.Contact_Info contactinfo ON contactinfo.WorkerID = we.WorkerID
   INNER JOIN VersionDetails vd ON   vd.RequestTableID = rd.RequestTableID
   WHERE we.AssignmentEndDate >= '2/16/2016' AND EndDate <= '2/20/2016' AND contactinfo.FirstName = 'Barsegyan' AND we.UpdatedDate = (SELECT MAX(we.UpdatedDate))
   GROUP BY wd.SAPID, rd.RequestID, vd.VersionTableID,rd.ProjectReference,contactinfo.FirstName,contactinfo.LastName,
                 wd.ActualEndDate, wd.IsIndependentContractor,we.AssignmentStartDate, EndDate, vd.VersionNo, we.CreatedDate, we.UpdatedDate

A sample of the data I'm querying:

And the error:

Answer 1

AND we.UpdatedDate = (SELECT MAX(we.UpdatedDate)) is the offending statement.

Two easy ways to solve this are to get the Max(we.UpdateDate) in a variable first, or add an inner join (select max(we.UpdateDate) from WorkerExtension ) and joing that back to the "we" aliased table.

Answer 2

As a general rule you want to put any aggregate conditions into the HAVING clause. That's what it's there for. However in this case it appears you are looking for the last updated record for the query. This is a fairly common task and there are a couple of ways you can go about it. The simplest (and from some tests I've run still one of the fastest) is to use a correlated subquery. Still in the WHERE clause by the way.

AND we.UpdatedDate = (SELECT MAX(we1.UpdatedDate) 
          FROM WorkerExtension we1 WHERE we1.WorkerId = wd.WorkerId
                          AND we1.AssignmentEndDate >= '2/16/2016')

Notice that I have a FROM here and I've re-referenced the WorkerExtension table with a new alias. This is to keep it separate from the version that you grouped on. If you reference we then you will hit the same version of the table that's in your query and all of the conditions that apply to it. Including your group by. And since you are grouping on the UpdatedDate (among other things) the MAX in we.UpdatedDate = (SELECT MAX(we.UpdatedDate)) is ALWAYS going to be the same as the first part of the comparison.

Also note that I included the other conditions in the WHERE clause that affect WorkerExtension. This is so that the rows used for the MAX have those conditions applied. This may or may not be required in your case.

---

Now if on the other hand you want only those rows that were updated on the very last UpdateDate in the WorkerExtension table then you don't want to corriliate the query.

AND we.UpdatedDate = (SELECT MAX(we1.UpdatedDate) 
          FROM WorkerExtension we1 WHERE we1.AssignmentEndDate >= '2/16/2016')

You still want to include any conditions that are important to you and you still want to reference a new version of the table.