clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798208 fiddles created (41244 in the last week).

CREATE TABLE dbo.Auctions ( ID INT NOT NULL IDENTITY(1, 1) CONSTRAINT pk_Auctions PRIMARY KEY CLUSTERED, Code CHAR(1) NOT NULL CONSTRAINT ck_Auctions_Code CHECK (Code = 'D' OR Code = 'S'), Quantity DECIMAL(19, 6) NOT NULL CONSTRAINT ck_Auctions_Quantity CHECK (Quantity > 0) ); SET IDENTITY_INSERT dbo.Auctions ON; INSERT INTO dbo.Auctions(ID, Code, Quantity) VALUES (1, 'D', 5.0), (2, 'D', 3.0), (3, 'D', 8.0), (5, 'D', 2.0), (6, 'D', 8.0), (7, 'D', 4.0), (8, 'D', 2.0), (1000, 'S', 8.0), (2000, 'S', 6.0), (3000, 'S', 2.0), (4000, 'S', 2.0), (5000, 'S', 4.0), (6000, 'S', 3.0), (7000, 'S', 2.0); SET IDENTITY_INSERT dbo.Auctions OFF;
14 rows affected
 hidden batch(es)


SELECT DemandID = d.ID, SupplyID = s.Id, TradeQuantity = IIF(d.CumeQty > s.CumeQty, s.CumeQty, d.CumeQty) - IIF(d.PrevCumeQty > s.PrevCumeQty, d.PrevCumeQty, s.PrevCumeQty) FROM ( SELECT *, CumeQty = SUM(Quantity) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING), PrevCumeQty = SUM(Quantity) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING) - Quantity FROM Auctions d WHERE Code = 'D' ) d JOIN ( SELECT *, CumeQty = SUM(Quantity) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING), PrevCumeQty = SUM(Quantity) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING) - Quantity FROM Auctions d WHERE Code = 'S' ) s ON s.CumeQty > d.PrevCumeQty AND d.CumeQty > s.PrevCumeQty
DemandID SupplyID TradeQuantity
1 1000 5.000000
2 1000 3.000000
3 2000 6.000000
3 3000 2.000000
5 4000 2.000000
6 5000 4.000000
6 6000 3.000000
6 7000 1.000000
7 7000 1.000000
 hidden batch(es)