USE newdb
GO

DROP function udf_partsOnOrders
GO

create function udf_partsOnOrders( @partNumber INT )
returns table 
return (SELECT [order] FROM t_orderLineItem
 WHERE @partNumber = partNumber )
GO

-- USE case: parts needed to fulfill orders 
SELECT * FROM t_parts CROSS APPLY 
	dbo.udf_partsOnOrders(partNumber)
GO

-- USE case: kit list, how many of each part to fulfill orders
SELECT partNumber, count(partNumber) FROM t_parts CROSS APPLY 
	dbo.udf_partsOnOrders(partNumber) GROUP BY partNumber
GO

-- USE case: show parts that can be returned to stock (i.e no orders USE them)
SELECT * FROM t_parts OUTER APPLY 
	dbo.udf_partsOnOrders(partNumber) WHERE [order] IS NULL
GO





