Improve Sql query with select max () function in where section

Question

The purpose of this request is to return products and their prices for products on sale, and the price should be from the closest date, but not equal to the date accepted mainly at the latest available price. There are no price records for each day. Something seems wrong in that the where clause contains an aggregate select statement. Is there a better way to do this? Maybe in the criteria for accession?

        select  
        p.ProductName,
        pp.Price,
        pp.Date,
        from product p
        inner join productprice pp  on p.productid = pp.productid
        where 
        pp.evaluationdate = (select  max(Date) from productprice 
                             where productid = p.productid  
                             and date < @DateIn) 
        and p.producttype = 'OnSale'

The actual query is a little more complicated, but this is essentially the problem. Thanks for your input.

EDIT More than one product will be returned.

@Remus Rusanu @km ( @Remus Rusanu ) , , , . , - , - , , .., - . .

Answer 1

:

;WITH CurrentPrice AS 
(
SELECT productid,max(Date) AS Date
    FROM productprice 
    WHERE date < @DateIn 
    GROUP BY productid
)

select  
    p.ProductName,
    pp.Price,
    pp.Date,
    from product p
        inner join CurrentPrice pa  on p.productid = pa.productid
        inner join productprice pp  on pa.productid = pp.productid AND pa.Date=pp.Date
    where p.producttype = 'OnSale'

OP:

, CTE , @Remus Rusanu

, productprice , "OnSale", :

;WITH CurrentPrice AS 
(
select  
    p.productid,
    MAX(pp.Date) AS Date
    from product p
        inner join productprice pp  on pa.productid = pp.productid
    where p.producttype = 'OnSale' AND pp.date < @DateIn 
    GROUP BY productid
)
select  
    p.ProductName,
    pp.Price,
    pp.Date,
    from CurrentPrice           pa
        inner join product      p   on pa.productid = p.productid
        inner join productprice pp  on pa.productid = pp.productid AND pa.Date=pp.Date
    where p.producttype = 'OnSale'

Answer 2

?

    SELECT * FROM (select  
            p.ProductName,
            pp.Price,
            pp.Date,
            RANK() OVER(PARTITION BY p.ProductId ORDER BY pp.Date DESC) as row_rank
            from product p
              join productprice pp  on p.productid = pp.productid
            where 
              pp.date < @DateIn
              and p.producttype = 'OnSale'
    ) saleprice
    where row_rank = 1

id ( , ), partiton

Answer 3

SELECT TOP 1 p.ProductName, pp.Price, pp.Date,
FROM product p
INNER JOIN productprice pp on ...
WHERE pp.date < @DateIn
ORDER BY pp.date DESC