SQL Reconcillation Query

Need assistance with SQL Query which can reconcile multiple entries (Buy/Sell) from one table to single line entries, tracking the amount bought and sold in Qty and Qty Left Columns:

I have a table as follows:

CREATE TABLE [dbo].[Trades(
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[ExecId] [varchar](35) NULL,
[Side] [varchar](6)开发者_运维知识库 NULL,
[Symbol] [varchar](35) NULL,
[LastQty] [varchar](35) NULL,
[LastPrice] [varchar](25) NULL,
[LeftQty] [varchar](35) NULL,
[Date] [varchar](35) NULL
)

with Entries:

 Id    Side Symbol  Qty    LastPrice  LeftQty   Date
 1     Buy    ABC   100     10.00       0       1/1/2011
 2     Sell   ABC   100     12.00       0       1/1/2011
 3     Sell   XYZ   200     8.00        0       1/1/2011
 4     Buy    XYZ   100     7.00        100     1/1/2011

How can I query table to produce a reconcillation report from the previous data as follows:

 Id  Side  Symbol  EntQty  EntPrice EntQty EntLeft ExtSide ExtQty ExtPrice  ExtLeft
  1  Buy    ABC     100     10.00   100     0       Sell    100     12.00      0
  2  Sell   XYZ     200     8.00    200     0       Buy     100      7.00    100

I need to combine opposite buy/sell entries onto the same line and then track the amount (Qty) remaining if the Buy Qty does not equal the Sell Qty

I assume that you can have 1 sell entry and 1 buy entry and always both. If you can have only one of them, you need to use a left or right join depending which one is always present.

The Id can't be returned as you have more than one and you don't have any criteria to select one of them.

EDITED: We obtain for each symbol:

• For boughts: total quantity, average price and the left quantity
• For sells: total quantity, average price and the left quantity
• The remains quantity (boughts total left quantity - sells total left quantity)

If you want an entry for each different pair buy-sell, you would need something to join the pair of entries (some common field). As you haven't got any field to join them, I think it is no possible to do on this way.

You can obtain all the info for each symbol on a single row, as the query does.

SELECT s.Symbol as Symbol, 
       b.SQty as EntQty, b.APrice as EntPrice, b.SLeft as EntLeft,
       s.SQty as ExtQty, s.APrice as ExtPrice, s.SLeft as ExtLeft,
       b.Qty - s.Qty as Remain                  
  FROM 
      (SELECT Symbol, 
              sum(Qty) as SQty, avg(Price) as APrice, sum(LeftQty) as SLeft 
         FROM Trades WHERE Side = 'Sell' GROUP BY Symbol) s
      INNER JOIN 
      (SELECT Symbol, 
              sum(Qty) as SQty, avg(Price) as APrice, sum(LeftQty) as SLeft 
         FROM Trades WHERE Side = 'Buy' GROUP BY Symbol) b
      ON s.Symbol = b.Symbol

The easiest would be:

• Group data by symbol and side,
• Do a self join on the result set on the results

For example:

WITH DATA AS 
(
  SELECT Symbol, Side, Sum(EntPrice), Sum(EntLength) 
    FROM Trades
   GROUP BY Symbol, Side
)
SELECT Symbol, -- Calculation goes here
  FROM DATA Sell
  JOIN DATA Buy ON Sell.Symbol = Buy.Symbol
 WHERE Sell.Side = SELL AND Buy.Side = BUY

If there is not always a buy and sell line for each symbol, perform a cross join and handle nulls properly in the calculations