By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
CREATE TABLE Warehouse(
ProdId int NOT NULL,
ProdName varchar(50) NOT NULL
);
INSERT into Warehouse (ProdId, ProdName) VALUES (1, 'Prod007');
INSERT into Warehouse (ProdId, ProdName) VALUES (2, 'Prod008');
INSERT into Warehouse (ProdId, ProdName) VALUES (3, 'Prod008');
INSERT into Warehouse (ProdId, ProdName) VALUES (4, 'Prod012');
INSERT into Warehouse (ProdId, ProdName) VALUES (5, 'Prod009');
INSERT into Warehouse (ProdId, ProdName) VALUES (6, 'Prod014');
INSERT into Warehouse (ProdId, ProdName) VALUES (7, 'Prod015');
7 rows affected
SELECT * FROM WAREHOUSE;
| ProdId | ProdName |
|---|---|
| 1 | Prod007 |
| 2 | Prod008 |
| 3 | Prod008 |
| 4 | Prod012 |
| 5 | Prod009 |
| 6 | Prod014 |
| 7 | Prod015 |
CREATE TABLE orders(
OrderNo varchar(50) NOT NULL,
RequiredProdName varchar(50) NOT NULL
) ;
INSERT INTO orders (OrderNo, RequiredProdName) VALUES ('ORD001', 'Prod007');
INSERT INTO orders (OrderNo, RequiredProdName) VALUES ('ORD001', 'Prod008');
INSERT INTO orders (OrderNo, RequiredProdName) VALUES ('ORD001', 'Prod009');
INSERT INTO orders (OrderNo, RequiredProdName) VALUES ('ORD002', 'Prod008');
INSERT INTO orders (OrderNo, RequiredProdName) VALUES ('ORD002', 'Prod009');
INSERT INTO orders (OrderNo, RequiredProdName) VALUES ('ORD002', 'Prod012');
INSERT INTO orders (OrderNo, RequiredProdName) VALUES ('ORD003', 'Prod008');
INSERT INTO orders (OrderNo, RequiredProdName) VALUES ('ORD003', 'Prod014');
8 rows affected
SELECT * FROM ORDERS;
| OrderNo | RequiredProdName |
|---|---|
| ORD001 | Prod007 |
| ORD001 | Prod008 |
| ORD001 | Prod009 |
| ORD002 | Prod008 |
| ORD002 | Prod009 |
| ORD002 | Prod012 |
| ORD003 | Prod008 |
| ORD003 | Prod014 |
WITH orders1 AS (
select orderno, requiredprodname, rn_order, stock , demand , stock-demand as remaining from (
SELECT o.orderno, o.requiredprodname,
ROW_NUMBER() OVER (PARTITION BY o.orderno ORDER BY o.requiredprodname) AS rn_order,
stock , count(o.requiredprodname)over(partition by o.orderno, o.requiredprodname)as demand
FROM orders o left join (select prodname, count(*)As stock from warehouse
group by prodname)w on o.RequiredProdName=w.prodname)a
), cte as (
select orderno, requiredprodname, rn_order, stock , demand , remaining
from orders1
--where rn_order=1
UNION ALL
SELECT c.orderno, o.requiredprodname, c.rn_order, c.stock , c.demand ,
c.remaining + c.stock - o.demand AS remaining
FROM orders1 o
INNER JOIN CTE c ON o.orderno = c.orderno AND o.requiredprodname != o.requiredprodname
AND c.rn_order = o.rn_order - 1
)
SELECT * from cte;
| orderno | requiredprodname | rn_order | stock | demand | remaining |
|---|---|---|---|---|---|
| ORD001 | Prod007 | 1 | 1 | 1 | 0 |
| ORD001 | Prod008 | 2 | 2 | 1 | 1 |
| ORD001 | Prod009 | 3 | 1 | 1 | 0 |
| ORD002 | Prod008 | 1 | 2 | 1 | 1 |
| ORD002 | Prod009 | 2 | 1 | 1 | 0 |
| ORD002 | Prod012 | 3 | 1 | 1 | 0 |
| ORD003 | Prod008 | 1 | 2 | 1 | 1 |
| ORD003 | Prod014 | 2 | 1 | 1 | 0 |