CREATE OR ALTER FUNCTION productOfTheDay(@day DATE)
RETURNS TABLE 
AS RETURN
( 
SELECT product.Name, m.MaxQty, header.OrderDate 
FROM Production.Product AS product, Sales.SalesOrderDetail AS detail, Sales.SalesOrderHeader AS header,
(SELECT MAX(OrderQty) AS MaxQty FROM Sales.SalesOrderDetail AS detail,
Sales.SalesOrderHeader AS header WHERE header.SalesOrderID = detail.SalesOrderID
AND OrderDate IN (SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE YEAR(@day) = YEAR(OrderDate) 
AND MONTH(@day) = MONTH(OrderDate) 
AND DAY(@day) = DAY(OrderDate))) AS m

WHERE product.ProductID = detail.ProductID 
AND header.SalesOrderID = detail.SalesOrderID 
AND OrderDate IN (SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE YEAR(@day) = YEAR(OrderDate) 
AND MONTH(@day) = MONTH(OrderDate) 
AND DAY(@day) = DAY(OrderDate))
AND detail.OrderQty = m.MaxQty
GROUP by m.MaxQty, product.Name, header.OrderDate
)