Mix And Match Rows

In the trading (stock market) industry there is a practice of rolling up (merging) multiple trades into a single trade in an effort to save on ticket charges. The way this is done is performing a SUM() on the quantities and calculating an average price. (Average price is the SUM(Qty * Price) / SUM(Qty).

So, given :

Qty     Price
20      $5
20      $10


You get:

40      $7.5           -- 20 + 20 and SUM(20 * $5, 20 * $10) / SUM(20 + 20)

Here is my dilema: If given a set of trades, I need to loop through them and check every combination to determine which one matches the expected rolled-up final trade. In other words,

If I know that the final trade is:

15     $10

And I have the following trades in my set:

TradeId     Qty     Price
1                10       $10
2                 7         $20
3                5          $10

I need to check the roll-up of trades (1, 2), (1, 3), and (2, 3) and determine that it final trade was made by rolling up trades 1 and 3.

In the real situation, the number of trades that I need to check is not set to a specific number.

Any help would be appreciated. Cursors, temp tables, functions, recursive calls, .NET (I am running SQL 2005 so have access to CLR) are ALL acceptable solutions...







Here is a sample SQL code (table and data) to work with.

USE [tempdb]

DROP TABLE [Trades]
GO

CREATE TABLE [Trades] (
[TradeId] INT,
[Quantity] INT,
[Price] DECIMAL(6,2)
)
GO

-- need to find trades that rollup to quantity 30 and average price 7.5

INSERT INTO [Trades] VALUES (1, 10, 10)
INSERT INTO [Trades] VALUES (2, 10, 5.0)
INSERT INTO [Trades] VALUES (3, 25, 7.5)
INSERT INTO [Trades] VALUES (4, 10, 10.0)
INSERT INTO [Trades] VALUES (5, 2, 2.0)
INSERT INTO [Trades] VALUES (6, 10, 7.5)

SELECT
[TradeId],
[Quantity],
[Price],
[Quantity] * [Price] AS [MarketValue]
FROM
[Trades]

-- need to find the trades that roll up to quantity 30 with an average price of $7.5
-- Trades 2, 4, and 6 are the solution

SELECT
SUM([Quantity]) AS [TotalQuantity],
SUM([Quantity] * [Price]) / SUM([Quantity]) AS [AveragePrice]
FROM
[Trades]
WHERE
[TradeId] IN (2, 4, 6)

-- what I need to get back is the SET of rows that make up the rolled trade, so I want to see

SELECT
*
FROM
[Trades]
WHERE
[TradeId] IN (2, 4, 6)


Thank you for any and all help in this.

- Jason