Use Atlantic;
GO
--SELECT *
--FROM dbo.v_Atlantic_Orders;
--GO
IF EXISTS
(SELECT *
		FROM sys.objects
		WHERE object_id = object_id(N'udf_Destinations_By_City')	
			AND type IN(N'FN', N'TF', N'IF')
)
DROP FUNCTION udf_Destinations_By_City
GO
CREATE FUNCTION udf_Destinations_By_City(@city CHAR(50))
RETURNS
@City_Destinations TABLE
(
 Cust_City CHAR(50)
, Destination_Name CHAR(50)
, Orders_Placed INT
, Current_Sell CHAR(100)
, New_Sell CHAR(100)
)
AS
BEGIN
	INSERT INTO @City_Destinations
	SELECT AO.cust_city
	, D.destination_name
	, COUNT(AO.orders_id) AS Orders_Placed
	, FORMAT(SUM((orders_qty * product_cost_price)), 'C', 'un-us') AS Current_Sell
	, FORMAT(SUM((orders_qty * product_cost_price) * 1.25), 'C', 'un-us') AS New_Sell
	FROM v_Atlantic_Orders AS AO INNER JOIN Customer AS C
		ON AO.customer_id = C.customer_id
		INNER JOIN Destination AS D
		ON AO.destination_id = D.destination_id
	WHERE C.cust_city = @city
	GROUP BY AO.cust_city, D.destination_name

RETURN
END
GO
