sql server queries returned incorrect results

The key issue starts with the price table – from your question as

Price (prodID, from, price)

You really want this to be in the format of a date range e.g., prodID, dateFrom, dateTo, price.

You can do this with a LEAD function (getting the next ‘from’, and calling it ‘dateto’)

SELECT prod_ID, [from] as datefrom, LEAD([from],1) OVER (PARTITION BY prodID ORDER BY [from]) AS dateto, [price]
FROM Price

To get the relevant price for a given order date, use the above as a virtual table rather than the price table and find the price as at the relevant date (between the datefrom and dateto, with the link to dateto being a ‘less than’ rather than ‘less than or equal to’) e.g.,

select o.orderID, s.price AS Shipping_price, sum(PO.amount * p.price) as item_Price
from [Order] as o
inner join PO as po on o.orderId = po.orderId
inner join Shipping as s on o.shipId = s.shipId
inner join 
    (SELECT prod_ID, [from] as datefrom, LEAD([from],1) OVER (PARTITION BY prodID ORDER BY [from]) AS dateto, [price]
    FROM Price
    ) as p on p.prodId = po.prodId AND o.[date] >= p.[datefrom] AND o.[date] < [dateto]
group by o.orderId, s.price
order by o.orderId;

Note in the above

• Total cost for items is calculated as item price * number of items (not just the base price for the item)
• I have separated shipping cost in the above – I imagine that it is by order (rather than by item) whereas your question includes the shipping once per item.

Note that @Dale’s comments are important – the above code is completely untested and may have SQL errors etc. As we don’t have sample data etc, I cannot run it to ensure it works. But hopefully it sets you on the correct path.

I do suggest you run a logic check over your result when finished e.g., if you order 5 bananas @ $1.50 each, with shipping $8, your total cost should be $15.50