clear markdown compare help best fiddles feedback dbanow.uk
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. 2591627 fiddles created (45702 in the last week).

CREATE TABLE dbo.Containers ( ContainerID int NOT NULL CONSTRAINT PK_containers PRIMARY KEY CLUSTERED , ContainerName text NOT NULL , ProductID int NOT NULL , Lot int NULL , Quantity int NOT NULL , ParentContainerID int NULL CONSTRAINT FK_Containers_ContainerID FOREIGN KEY REFERENCES dbo.Containers (ContainerID) ON UPDATE NO ACTION ON DELETE NO ACTION ); CREATE TABLE dbo.OrdersItems ( OrderID INT NOT NULL , Position int NOT NULL , ContainerID int NULL CONSTRAINT FK_OrdersItems_ContainerID FOREIGN KEY REFERENCES dbo.Containers (ContainerID) ON UPDATE NO ACTION ON DELETE NO ACTION , CONSTRAINT PK_OrdersItems PRIMARY KEY CLUSTERED (OrderID, Position) ); INSERT INTO dbo.Containers (ContainerID, ContainerName, ProductID , Lot, Quantity, ParentContainerID) VALUES (23, 'Box 40', 2, null, 40, null) , (16, 'Pallet', 1, null, 120, null) , (12, 'Bag 20', 1, null, 20, 14) , (13, 'Bag 20', 1, null, 20, 14) , (14, 'Box 40', 1, null, 40, 16) , (19, 'Bag 20', 2, null, 20, 23) , (22, 'Bag 20', 2, null, 20, 23) , (5, 'Bag 20', 1, null, 20, 7) , (6, 'Bag 20', 1, null, 20, 7) , (7, 'Box 40', 1, null, 40, 16) , (1, 'Bag 10', 1, 500, 10, 5) , (2, 'Bag 10', 1, 501, 10, 5) , (3, 'Bag 10', 1, 502, 10, 6) , (4, 'Bag 10', 1, 500, 10, 6) , (8, 'Bag 10', 1, 600, 10, 12) , (9, 'Bag 10', 1, 601, 10, 12) , (10, 'Bag 10', 1, 502, 10, 13) , (11, 'Bag 10', 1, 501, 10, 13) , (15, 'Box 40', 1, 600, 40, 16) , (17, 'Bag 10', 2, 700, 10, 19) , (18, 'Bag 10', 2, 701, 10, 19) , (20, 'Bag 10', 2, 703, 10, 22) , (21, 'Bag 10', 2, 701, 10, 22); INSERT INTO dbo.OrdersItems (OrderID, Position, ContainerID) VALUES (1, 1, 16) , (1, 2, 23);
25 rows affected
 hidden batch(es)


;WITH RecursiveCTE AS ( SELECT c1.ContainerID , c1.ContainerName , c1.Lot , c1.ParentContainerID , c1.ProductID , c1.Quantity , Level = 1 FROM dbo.Containers c1 WHERE c1.ParentContainerID IS NULL UNION ALL SELECT c2.ContainerID , c2.ContainerName , c2.Lot , c2.ParentContainerID , c2.ProductID , c2.Quantity , Level = RecursiveCTE.Level + 1 FROM dbo.Containers c2 INNER JOIN RecursiveCTE ON RecursiveCTE.ContainerID = c2.ParentContainerID ) SELECT r.ProductID , r.Lot , QuantityTotal = SUM(r.Quantity) FROM RecursiveCTE r WHERE r.Lot IS NOT NULL AND r.Level > 1 GROUP BY r.ProductID , r.Lot;
ProductID Lot QuantityTotal
1 500 20
1 501 20
1 502 20
1 600 50
1 601 10
2 700 10
2 701 20
2 703 10
 hidden batch(es)