USE SQL2008SBS
GO

CREATE FUNCTION Orders.fn_oldestopenorder()
RETURNS INT
AS
BEGIN
   DECLARE @OrderID         INT,
           @MinOrderDate    DATE
   
   SELECT @MinOrderDate = MIN(OrderDate)
   FROM Orders.OrderHeader
   WHERE FinalShipDate IS NULL
   
   SELECT @OrderID =  MIN(OrderID)
   FROM Orders.OrderHeader
   WHERE OrderDate = @MinOrderDate
   
   RETURN @OrderID
END
GO

CREATE FUNCTION Orders.fn_openorders (@NumDays  INT)
RETURNS TABLE
AS
RETURN
(SELECT OrderID, CustomerID, OrderDate
FROM Orders.OrderHeader
WHERE OrderDate <= DATEADD(dd,@NumDays,GETDATE())
    AND FinalShipDate IS NULL
)
GO

CREATE FUNCTION Orders.fn_openshippableorders(@NumDays  INT)
RETURNS @ShippableOrders TABLE
(OrderID    INT NOT NULL,
CustomerID  INT NOT NULL,
OrderDate   DATE NOT NULL)
AS
BEGIN
    DECLARE @OpenOrders TABLE
    (OrderID    INT NOT NULL,
    CustomerID  INT NOT NULL,
    OrderDate   DATE NOT NULL)

    INSERT INTO @OpenOrders
    (OrderID, CustomerID, OrderDate)
    SELECT OrderID, CustomerID, OrderDate
    FROM Orders.fn_openorders(@NumDays)
    
    INSERT INTO @ShippableOrders
    (OrderID, CustomerID, OrderDate)
    SELECT a.OrderID, a.CustomerID, a.OrderDate
    FROM @OpenOrders a INNER JOIN     
        (SELECT OrderID FROM Orders.OrderDetail
        EXCEPT
        SELECT OrderID FROM Orders.OrderDetail c
        INNER JOIN Products.ProductOptions d ON c.SKU = d.SKU
        INNER JOIN Products.ProductInventory e ON d.ProductID = e.ProductID
        WHERE c.Quantity >= e.Quantity) b ON a.OrderID = b.OrderID
    
    RETURN
END
GO

--Result1
SELECT OrderID, CustomerID, OrderDate
FROM Orders.OrderHeader
GO

--Result2
SELECT OrderID, OrderDetailID, SKU, Quantity 
FROM Orders.OrderDetail
GO

--Result3
SELECT a.SKU, a.ProductID, b.Quantity
FROM Products.ProductOptions a INNER JOIN Products.ProductInventory b ON a.ProductID = b.ProductID
GO

SELECT Orders.fn_oldestopenorder()
GO

--Result4
SELECT * FROM Orders.fn_openorders(1)
GO

--Result5
SELECT * FROM Orders.fn_openorders(5)
GO

ALTER FUNCTION Orders.fn_openorders (@NumDays  INT)
RETURNS TABLE
AS
RETURN
(SELECT OrderID, CustomerID, OrderDate
FROM Orders.OrderHeader
WHERE OrderDate <= DATEADD(dd,-@NumDays,GETDATE())
    AND FinalShipDate IS NULL
)
GO

--Result6
SELECT * FROM Orders.fn_openorders(1)
GO

--Result7
SELECT * FROM Orders.fn_openorders(5)
GO

--Result8
SELECT * FROM Orders.fn_openshippableorders(3)
GO

--Result9
SELECT * FROM Orders.fn_openshippableorders(5)
GO

UPDATE Products.ProductInventory SET Quantity = 15 WHERE ProductID = 2
GO

--Result10
SELECT * FROM Orders.fn_openshippableorders(3)
GO

--Result11
SELECT * FROM Orders.fn_openshippableorders(5)
GO