I have a master item made up of multiple components. The quantity available for this master item is equal to the lowest component quantity from all the components. I need to return that lowest quantity value for a report specifying the quantity of available master items. I can't figure out a way doing this in MS SSIS Query Designer.
Any suggestions for doing this are appreciated.
You are discovering GROUPing and aggregate functions.
As you already seem to have a working query to fetch individual components' quantity,
you can further divide the stock quantity by the number of needed items (SI.QuantityOnHand / KitDetail.Quantity),
and GROUP BY KitItem.ItemId while getting the minimum of the division's result for that group.
In SQL:
SELECT KitItem.ItemId, MIN(SI.QuantityOnHand / KitDetail.Quantity) QuantityOnHand
FROM KitDetail INNER JOIN
Item AS CompItem ON KitDetail.ItemId = CompItem.ItemId LEFT OUTER JOIN
StockItem AS SI ON SI.ItemId = CompItem.ItemId INNER JOIN
Kit ON KitDetail.KitId = Kit.KitId INNER JOIN
Item AS KitItem ON KitItem.ItemId = Kit.ItemId
WHERE
(KitItem.ItemId = @ItemId)
GROUP BY KitItem.ItemId;