使用where子句中的select max()函数改进Sql查询

The purpose of this query is to bring back products and their prices for products on sale and the price should be from the date closest but not equal to the date passed in, essentially the most recent price available. There are not price records for every day. Something feels a little wrong about having the aggregate select statement in the where clause. Is there a better way to do this? Maybe in the join criteria?The purpose of this query is to bring back prod