clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1716880 fiddles created (23882 in the last week).

CREATE TABLE dbo.Item ( Id integer NOT NULL, ParentId integer NULL, DisplaySeq integer NOT NULL, DisplayText varchar(30) NOT NULL, OnHandQty integer NOT NULL ); INSERT INTO Item (Id, ParentId, DisplaySeq, DisplayText, OnHandQty) VALUES (9, NULL, 0, 'Laptop & Bag Kit', 3), (8, 9, 5, 'Laptop Kit', 11), (7, 8, 10, 'Laptop', 5), (6, 8, 15, 'Power Supply', 4), (26, 9, 20, 'Bag', 23) ; -- Useful index CREATE INDEX i ON dbo.Item (ParentId) INCLUDE (Id, DisplaySeq, DisplayText, OnHandQty);
5 rows affected
 hidden batch(es)


CREATE TABLE #Items ( Id integer PRIMARY KEY, MPath varchar(255) NOT NULL, DisplayText varchar(30) NOT NULL, OnHandQty integer NOT NULL, [Level] integer NOT NULL, ParentId integer NOT NULL, MaxCanMake integer NULL, AvailToSell AS OnHandQty + MaxCanMake ); WITH Items AS ( SELECT I.Id, MPath = CONVERT(varchar(255), I.DisplaySeq), I.DisplayText, I.OnHandQty, 0 AS [Level], 0 AS ParentId FROM dbo.Item AS I WHERE I.ParentId IS NULL UNION ALL SELECT I.Id, CONVERT(varchar(255), Parent.MPath + '.' + CONVERT(varchar(11), I.DisplaySeq)), I.DisplayText, I.OnHandQty, Parent.[Level] + 1, I.ParentId FROM Items AS Parent JOIN dbo.Item AS I WITH (FORCESEEK) ON I.ParentId = Parent.Id ) INSERT #Items (Id, MPath, DisplayText, OnHandQty, [Level], ParentId) SELECT I.Id, I.MPath, I.DisplayText, I.OnHandQty, I.[Level], I.ParentId FROM Items AS I OPTION (MAXRECURSION 0); -- Useful index CREATE INDEX i ON #Items (ParentId, AvailToSell);
5 rows affected
 hidden batch(es)


SELECT * FROM #Items AS I ORDER BY I.MPath
Id MPath DisplayText OnHandQty Level ParentId MaxCanMake AvailToSell
9 0 Laptop & Bag Kit 3 0 0
26 0.20 Bag 23 1 9
8 0.5 Laptop Kit 11 1 9
7 0.5.10 Laptop 5 2 8
6 0.5.15 Power Supply 4 2 8
 hidden batch(es)


DECLARE @Level integer = ( SELECT MAX(I.[Level]) FROM #Items AS I ); WHILE @Level >= 0 BEGIN UPDATE I SET I.MaxCanMake = ISNULL ( ( SELECT TOP (1) I2.AvailToSell FROM #Items AS I2 WHERE I2.ParentId = I.Id ORDER BY I2.AvailToSell ASC ), 0 ) FROM #Items AS I WHERE I.[Level] = @Level; SET @Level -= 1; END;
5 rows affected
 hidden batch(es)


SELECT DisplayText = REPLICATE('---', I.[Level]) + I.DisplayText, I.OnHandQty, I.MaxCanMake, I.AvailToSell FROM #Items AS I ORDER BY I.MPath;
DisplayText OnHandQty MaxCanMake AvailToSell
Laptop & Bag Kit 3 15 18
---Bag 23 0 23
---Laptop Kit 11 4 15
------Laptop 5 0 5
------Power Supply 4 0 4
 hidden batch(es)