SQL Finding Order ID and Total Price of Each Order

I have a database where I have to determine the OrderID and total price of each order. The issue is finding the price on the specific day of the order. Every product has a price on a particular day using the "from" attribute. I need to get only the price of the product where the "from" attribute is less than the current "date" attribute.

This is what I have been trying.

SELECT
    O.orderId,
    (P.price + S.price) AS total_price
FROM Price P
    INNER JOIN PO ON PO.prodId = P.prodId
    INNER JOIN [Order] O ON PO.orderId = O.orderId
    INNER JOIN Shipping S ON S.shipId = O.shipId
GROUP BY
    O.orderId,
    O.[date],
    P.price,
    S.price
HAVING O.[date] IN (
        SELECT
            O.[date]
        FROM
            [Order]
        WHERE
            MAX(P.[from]) <= O.date
    )
ORDER BY  orderId;

I still get some duplicates from this and it is not giving me every orderId.

from Recent Questions - Stack Overflow https://ift.tt/3wH5nYJ
https://ift.tt/eA8V8J