(

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;

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