SQL query to get the purchase price before the sale

Check it and you confirm how you are doing with this report ... it would be nice to create views that help many of the processes ....

SELECT 
    P.id, P.descripcion, 
    V2.fechaventa, V2.fv_id, V2.id_producto, V2.valor_venta, 
    C2.id as fc_id, C2.fecha_compra, IFNULL(C2.precio_compra * V2.cantidad, 0) AS valor_compra
FROM (
    SELECT 
        MAX(C1.fecha_compra) AS fecha_compra, 
        V1.fechaventa, V1.fv_id, V1.id_producto, SUM(V1.cantidad) AS cantidad, SUM(V1.valor_total) AS valor_venta
    FROM (
        SELECT FV.fechaventa, FV.id AS fv_id, VP.id_producto, VP.cantidad, (VP.valor * VP.cantidad) AS valor_total
        FROM venta_producto AS VP
            INNER JOIN factura_venta AS FV 
                ON FV.id = VP.id_venta
        ) AS V1
        LEFT OUTER JOIN (
            SELECT FC.fecha_compra, PC.id_producto
            FROM producto_compra AS PC
                INNER JOIN factura_compra AS FC
                    ON PC.id_compra = FC.id
        ) AS C1
        ON C1.id_producto = V1.id_producto
        AND C1.fecha_compra < V1.fechaventa
    GROUP BY V1.fechaventa, V1.fv_id, V1.id_producto
) AS V2
    INNER JOIN producto AS P 
        ON P.id = V2.id_producto
    LEFT OUTER JOIN (
        SELECT Fc.id, FC.fecha_compra, PC.id_producto, AVG(PC.precio_compra) AS precio_compra
        FROM producto_compra AS PC
            INNER JOIN factura_compra AS FC
                ON PC.id_compra = FC.id
        GROUP BY Fc.id, FC.fecha_compra, PC.id_producto
    ) AS C2
        ON C2.id_producto = V2.id_producto
        AND C2.fecha_compra = V2.fecha_compra
ORDER BY P.id, P.descripcion, V2.fechaventa, V2.fv_id, V2.id_producto;

How are you?

If I understood your doubt well, this code solves it; the first query creates a temporary table that tells us which purchases apply as made before the sale date and flag them. The second one, which is the definitive one, filters the records with the flag and only returns the maximum purchase date before the sale date.

SELECT
p.id as [Pid]
,p.descripcion as [descripcion]
,(vp.valor * vp.cantidad) as [valor_total]
,fv.id as [FVid]
,fv.fechaventa as [fechaventa]
,fc.id as [FCid]
,fc.fecha_compra as [fechacompra]
,(pc.precio_compra * vp.cantidad) as [totalcantidad]
,CASE
    WHEN fc.fecha_compra < fv.fechaventa THEN 1
    ELSE 0
 END AS [ValidaFechaCompra]
 into #tablaTMP1
FROM venta_producto vp
inner join factura_venta fv on fv.id = vp.id_venta
inner join producto p on p.id = vp.id_producto
left join producto_compra pc on pc.id_producto = p.id
left join factura_compra fc on fc.id = pc.id_compra
group by fv.id, p.id

select
 #tablaTMP1.[Pid]
 , max(#tablaTMP1.[fechacompra])
 ,tmp2.valor_total
 ,tmp2.totalcantidad
 from #tablaTMP1 inner join (select [Pid],[fechacompra], [valor_total], [totalcantidad] from #tablaTMP1) as tmp2
    on #tablaTMP1.Pid = tmp2.Pid and max(#tablaTMP1.[fechacompra]) = tmp2.fechacompra
where #tablaTMP1.ValidaFechaCompra = 1

Did you only try to add a Having ?

The Having is like a Where for aggregated data, it would be something like:

SELECT 
    p.id, p.descripcion, (vp.valor * vp.cantidad) as valor_total ...
.....
Where 
    fc.fecha_compra < fv.fechaventa
ORDER BY p.id, fv.id ASC
HAVING MAX(fc.fecha_compra)

This should help you.