By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Orders (
OrderNo VARCHAR(10),
RequiredProdName VARCHAR(10)
);
CREATE TABLE Warehouse (
ProdId INT,
ProdName VARCHAR(10)
);
INSERT Orders
VALUES -- initial / remaining / allocated / remaining
('ORD001', 'Prod007'), -- 1 1 1 0
('ORD001', 'Prod008'), -- 2 2 1 1
('ORD001', 'Prod009'), -- 1 1 1 0
--
('ORD002', 'Prod008'), -- 2 1 0 1
('ORD002', 'Prod009'), -- 1 0 0 0
('ORD002', 'Prod012'), -- 1 1 0 1
--
('ORD003', 'Prod008'), -- 2 1 1 0
('ORD003', 'Prod014'); -- 1 1 1 0
INSERT Warehouse
VALUES
(1, 'Prod007'),
(2, 'Prod008'),
(3, 'Prod008'),
(4, 'Prod012'),
(5, 'Prod009'),
(6, 'Prod014'),
(7, 'Prod015');
15 rows affected
-- First, normalize the data into
WITH CTE_Orders AS (
SELECT
X.OrderNo,
ROW_NUMBER() OVER(ORDER BY X.OrderNo) AS OrderSeq
FROM Orders X
GROUP BY X.OrderNo
),
CTE_Products AS (
-- Note: Warehouse products not referenced from orders are omitted
SELECT
X.RequiredProdName AS ProdName,
ROW_NUMBER() OVER(ORDER BY X.RequiredProdName) AS ProductSeq
FROM Orders X
GROUP BY X.RequiredProdName
),
CTE_OrderProducts AS (
SELECT O.OrderSeq, P.ProductSeq, COUNT(*) AS Quantity
FROM Orders X
JOIN CTE_Orders O ON O.OrderNo = X.OrderNo
JOIN CTE_Products P ON P.ProdName = X.RequiredProdName
GROUP BY O.OrderSeq, P.ProductSeq
),
CTE_Inventory AS (
-- Note: Warehouse products not referenced from orders are omitted
SELECT P.ProductSeq, COUNT(*) AS InitialStock
FROM CTE_Products P
JOIN Warehouse W
ON W.ProdName = P.ProdName
GROUP BY P.ProductSeq
),
CTE_OrderFullfilment AS (
-- Seed the recursive CTE with an OrderSeq = 0 row containing the initial
-- encoded in a comma-separated list. An initial element is inserted at
-- the front as the OrderFulfilled flag - fixed at 0 for this seed row.
| OrderNo |
|---|
| ORD001 |
| ORD003 |