February 10, 2011 at 4:29 am
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,
[CummQty] [varchar](35) NULL,
[LeftQty] [varchar](35) NULL,
[Date] [varchar](35) NULL
)
with Entries:
IdSideSymQtyLastPri LeftQtyDate
1BuyABC10010 01/1/2011
2SellABC10012 01/1/2011
3SellXYZ2008 01/1/2011
4BuyXYZ1007 1001/1/2011
How can I query table to produce a reconcillation report from the previous data as follows:
IdSideSymbolSymbolEntPricEntQtyExtSideExtQtyExtPriExtLeft
1BuyABC10010100 Sell100100
2SellXYZ2008200 Buy1008100
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
February 10, 2011 at 4:57 am
YOu can use the CASE statement to get the column based data.
Abhijit - http://abhijitmore.wordpress.com
February 10, 2011 at 5:48 am
There are 100s or 1000s of entries a CASE statement would not be enough to combine BUY SELL entries
February 10, 2011 at 6:25 am
OK
This should get you started.
This assumes that a symbol will appear twice, once for the buy side, and once for sell. If either is missing, the row will not be returned. If a symbol can have more than one buy or sell, then you will need to use a group by clause and aggregate functions. If a symbol can have only a buy or sell side, then you will need to look at using a full outer join and coalescing nulls for ExtLeft.
I'm not sure about the purpose of the LeftQty column in trades. Is that the value you want in ExtLeft? If so put that in the query. As it is ExtLeft will be positive or negative depending upon whether you are long or short of an instrument. This would make more sense.
I haven't got SQL Server on my machine so you may need to check the syntax. Anyway, as I said it's a start based on the info you gave and should get you going.
SELECT t1.id, t1.side,t1.symbol,t1.LastPri EntPric,t2.Side ExtSide, t2.qty extQty, t2.LastPri ExtPri, t1.Qty-t2.Quantity ExtLeft
FROM Trades t1
JOIN Trades T2
ON t1.Symbol= t2.symbol
WHERE t1.Side = 'Buy'
AND t2.side = 'Sell'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply