1

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.

1

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

  • I will try to rewrite the question. Just to make it more readable. Thank you very much. – McNets Dec 28 '16 at 18:47
  • I'm glad I was able to help. – Max Vernon Dec 28 '16 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.