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.
Simplified Query Result
Any suggestions for doing this are appreciated.
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.
Simplified Query Result
Any suggestions for doing this are appreciated.
Share Improve this question edited Mar 22 at 2:29 Dale K 27.5k15 gold badges58 silver badges83 bronze badges asked Mar 21 at 15:51 user2985312user2985312 113 bronze badges 3- 1 No idea what you talk about. Please provide both sample input data and expected result as tables with text in your question. Do not use screenshots or links because no one can copy the data to try out possible solutions. Also provide the query you have tried as text. – Jonas Metzler Commented Mar 21 at 15:53
- 1 Please do not post images see here for how to ask a good question: meta.stackoverflow/questions/271055/… – Brad Commented Mar 21 at 15:54
- 1 Are you wondering how to do it in MS SSIS, or in SQL? I suppose you meant in SQL, else wouldn't have you provided us with the SQL query you are trying to build? – Guillaume Outters Commented Mar 21 at 16:47
1 Answer
Reset to default 1You 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;
(here running on a small example data set)