I have a T-SQL query that is designed to weed out duplicate entries of a certain product training, grabbing only the one with the most recent DateTaken. For example, if someone has taken a certain training course 3 times, we only want to display one row, that row being the one that contains the most recent DateTaken. Here is what I have so far, however I am receiving the following error:
An expression of non-boolean type specified in a context where a condition is expected, near 'ORDER'.
The ORDER BY is necessary since we want to group all the results of this query by the expiration date. Below is the full query:
SELECT DISTINCT p.ProductDescription as ProductDesc, c.CourseDescription as CourseDesc, c.Partner, a.DateTaken, a.DateExpired, p.Status FROM sNumberToAgentId u, AgentProductTraining a, Course c, Product p WHERE @agentId = u.AgentId and u.sNumber = a.sNumber and a.CourseCode = c.CourseCode and (a.DateExpired >= @date or a.DateExpired IS NULL) and a.ProductCode = p.ProductCode and (p.status != 'D' or p.status IS NULL) GROUP BY (p.ProductDescription) HAVING MIN(a.DateTaken) ORDER BY DateExpired ASC
I've made the following changes to the GROUP BY and HAVING clauses, however I am still receiving errors:
GROUP BY (p.ProductDescription, c.CourseDescription) HAVING MIN(a.DateTaken) > GETUTCDATE()
In SQL Management Studio, and red line error marker appears under the ',' after p.ProductDescription, the ')' after c.CourseDescription, the 'a' in a.DateTaken, and the closing parenthesis ')' of GETUTCDATE(). If I simply leave the GROUP BY statement to include only p.ProductDescription I get this error message:
Column 'Product.ProductDescription' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I'm relatively new to SQL, could someone explain what's going on? Thank you!
My suggestion since you are using sql server is to implement
row_number() and partition by the
CourseDescription. This will go in a subquery and then you apply a filter to return only those where the row number is equal to one or the most recent record:
select * from ( SELECT p.ProductDescription as ProductDesc, c.CourseDescription as CourseDesc, c.Partner, a.DateTaken, a.DateExpired, p.Status row_number() over(partition by p.ProductDescription, c.CourseDescription order by a.DateTaken desc) rn FROM sNumberToAgentId u INNER JOIN AgentProductTraining a ON u.sNumber = a.sNumber AND (a.DateExpired >= @date or a.DateExpired IS NULL) INNER JOIN Course c ON a.CourseCode = c.CourseCode INNER JOIN Product p ON a.ProductCode = p.ProductCode AND (p.status != 'D' or p.status IS NULL) WHERE u.AgentId = @agentId ) src where rn = 1 order by DateExpired