Chapter 7 Exercise 15

15. Write a function to compute the average purchase cost of an item over the prior year and provide a warning if the ListPrice of the merchandise is lower than that value.

Hinges on the concept that items can be purchased many times (from suppliers) at different costs. The key is to use a query to get the values needed for a weighted average: Sum(Cost*Qty)/Sum(Qty). Basic logic: (syntax needs to be corrected to run in a specific DBMS; mostly date and parameter syntax)
 
CREATE FUNCTION compareItemCost(ItemID Integer, newListPrice money)
BEGIN
DECLARE @Value money, @SumQty Integer
@StartDate datetime, @EndDate datetime;
-- dynamically compute the starting and ending dates based on today
SELECT GetDate()-365 INTO @StartDate, GetDate() INTO @
EndDate;
 
SELECT Sum(OrderItem.Quantity) INTO @SumQty,
Sum([Cost]*[Quantity]) INTO @Value
FROM MerchandiseOrder INNER JOIN OrderItem ON MerchandiseOrder.PONumber = OrderItem.PONumber
WHERE (MerchandiseOrder.OrderDate Between @StartDate And @EndDate)
AND (OrderItem.ItemID = @ItemID);
END;
IF (@newListPrice < @Value/@SumQty) THEN
  Return ERROR_PRICE_IS_TOO_LOW
END IF