Question :
Given the schema:
create table containers (
id int primary key,
name text,
product_id int,
lot int,
qty int,
parent_id int);
create table orders_items (
id int,
position int,
container_id int);
Where containers is a hierarchical table with an unknown number of levels.
containers.parent_id = containers.id OR null
A container can hold other containers, this is a containers tree structure:
23 Box 40 Parent [ID: NULL, Lot: NULL, Qty: SUM(20+20)=40]
|
|--22 Bag 20 [Parent ID: 23, Lot: NULL, Qty: SUM(10+10)=20]
| |--21 Bag 10 [Parent ID: 22, Lot: 701, Qty: 10]
| |--22 Bag 10 [Parent ID: 22, Lot: 703, Qty: 10]
|
|--19 Bag 20 [Parent ID: 23, Lot: NULL, Qty: SUM(10+10)=20]
|--17 Bag 10 [Parent ID: 19, Lot: 700, Qty: 10]
|--18 Bag 10 [Parent ID: 19, Lot: 701, Qty: 10]
For us a container is considered as one expeditionary unit, depending on customer requirements a container can be a simple plastic bag of 10 pieces, or a full pallet with 10 boxes of 5000 pieces each one. Only containers of last level has a lot assigned, parent containers can hold different lots, but always belonging to same product.
We need to list all products included in a single order, group by lot.
- Different products can’t have the same lot.
- One container can only hold containers of the same product.
Sample data:
insert into containers 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 orders_items values
(1, 1, 16),
(1, 2, 23);
The order number 1 has two details lines, containers 16& 23, I need to get all lots included in these containers.
In this example the result should show this rows:
+----+------------+-------------+------+------+------------+
| id | name | product_id | lot | qty | parent_id |
+----+------------+-------------+------+------+------------+
| 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 |
+----+------------+-------------+------+------+------------+
Group by lot:
|----------|---------|-----|----------|
| Order ID | Product | Lot | Quantity |
|----------|---------|-----|----------|
| 1 | 1 | 500 | 20 |
| 1 | 1 | 501 | 20 |
| 1 | 1 | 502 | 20 |
| 1 | 1 | 600 | 50 |
| 1 | 1 | 601 | 10 |
| 1 | 2 | 700 | 10 |
| 1 | 2 | 701 | 20 |
| 1 | 2 | 703 | 10 |
|----------|---------|-----|----------|
I’ve created a rextester example with this values.
Answer :
This code shows how to use a recursive CTE, to return the results you’re looking for.
IF OBJECT_ID('dbo.OrdersItems') IS NOT NULL DROP TABLE dbo.OrdersItems;
IF OBJECT_ID('dbo.Containers') IS NOT NULL DROP TABLE dbo.Containers;
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 the sample data:
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);
Here’s the recursive CTE:
;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;
The results:
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
As you can tell from the above code, I’ve renamed some of your columns to more clearly reflect their contents, and have added some minor formatting, along with foreign-key references.
db<>fiddle here