Answer to Question #180983 in Databases | SQL | Oracle | MS Access for Miley

Question

Help in doing this : Create a function that takes as inputs a SalesOrderID, a Currency Code, and a date, and returns a table of all the SalesOrderDetail rows for that Sales Order including Quantity, ProductID, UnitPrice, and the unit price converted to the target currency based on the end of day rate for the date provided. Exchange rates can be found in the Sales.CurrencyRate table. ( Use AdventureWorks)

Answer 1

SELECT ProductID, SUM((UnitPrice-(UnitPrice*UnitPriceDiscount/100))*OrderQty) AS "Total"

FROM Sales.SalesOrderDetail

GROUP BY ProductID

HAVING Total > 9000