Use Atlantic;
GO
IF EXISTS
(SELECT *
		FROM sys.objects
		WHERE object_id = object_id(N'udf_Customer_Last_Order')	
			AND type IN(N'FN', N'TF', N'IF')
)
DROP FUNCTION udf_Customer_Last_Order
GO
CREATE FUNCTION udf_Customer_Last_Order (@Customer_id INT) 
-- RETURNS to a table variable @CustomerOrder
RETURNS @CustomerOrder TABLE
(SaleOrder_id		INT         NOT NULL,
Customer_id			INT         NOT NULL,
OrderDate			DATETIME    NOT NULL,
OrderQty			INT         NOT NULL,
CustomerName		VARCHAR(20)	NOT NULL,
CustomerCity		VARCHAR(20)	NOT NULL
)
AS
BEGIN
    DECLARE @MaxDate DATETIME
    SELECT @MaxDate = MAX(orders_date)
    FROM Orders

    INSERT INTO @CustomerOrder	--insert into TABLE variable
	SELECT 	O.orders_id
	, C.customer_id
	, CAST(O.orders_date AS DATE) AS date_only
	, O.orders_qty
	, C.cust_firstname + ' ' + C.cust_lastname
	, C.cust_city
	FROM Customer AS C INNER JOIN Orders AS O
		ON C.customer_id = O.customer_id
		WHERE O.orders_date = (SELECT MAX(orders_date)	-- this returns all the MAX dates that match the @Customer_id variable
								FROM Orders
								WHERE customer_id = @Customer_id
							  ) AND C.customer_id = @Customer_id	-- this will filter only the MAX date for the @Customer_id
RETURN
END;