FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

declare @ItemId int = 0;

CREATE TABLE Kit (KitId INT, ItemId INT);
CREATE TABLE KitDetail (KitId INT, ItemId INT, Quantity INT);
CREATE TABLE Item (ItemId INT);
CREATE TABLE StockItem (ItemId INT, QuantityOnHand INT);

insert into Item values (0), (1), (2), (3), (4), (5);
insert into Kit values (1, 0);
insert into KitDetail values (1, 1, 3), (1, 2, 3), (1, 3, 3), (1, 4, 1), (1, 5, 20);
insert into StockItem values (1, 347), (2, 245), (3, 128), (4, 47), (5, 6000);

SELECT KitItem.ItemId, KitDetail.Quantity, SI.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);

SELECT KitItem.ItemId, MIN(SI.QuantityOnHand / KitDetail.Quantity) PossibleToBuild 
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;

ItemId	Quantity	QuantityOnHand
0	3	347
0	3	245
0	3	128
0	1	47
0	20	6000

ItemId	PossibleToBuild
0	42

(6 rows affected)

(1 rows affected)

(5 rows affected)

(5 rows affected)

(5 rows affected)

(1 rows affected)