Search code examples
sqlinventory-management

SQL PnL Inventory Query

I have a table containing a list of trades:

Security ;   Quantity ; Price ;  Consid
 1. IBM  ;    +1,000 ;   20  ;  -20k
 2. IBM  ;    +2,000 ;   22  ;  -44k
 3. IBM  ;    -1,000 ;   30  ;  +30k
 4. IBM  ;    -2,000 ;   20  ;  +40k
 5. IBM  ;    -2,000 ;   20  ;  -20k

So the PnL is basically the sum of the Consid column so before Trade#5 was added the PnL would be +6k.

After Trade #5 was added this shows the PnL as -14k which isnt really that reflective of where we stand.

What I would like is some way of filtering out the not closed trades? So Trade#5 will only be allowed into the sum when we have added a purchase of 2k IBM shares to the table.

My intial attempt at this was:

set @Ret = @Ret + isnull((SELECT SUM(GC) AS GS
FROM  (SELECT SUM(GrossConsid) * - 1 AS GC
                     FROM   Trades AS CT
                     WHERE  (SpecialCond = 'Prop') AND (SettType <> 'Futures') AND (TrdDt <= @Date) AND (TrdDt >=@StartDate) AND (Name = 'my_Comp')
                     GROUP BY ABS(Quantity)
                     HAVING (SUM(Quantity) = 0)) AS dt),0)

but I have no figured out that there is an edge condition where by if I have a trades with a Quantity of +5,+5,-5 it doesnt get counted because the (SUM(Quantity) = 0)) evaluates to false.

Any ideas on how I can rectify this?

Thanks Chris

Solution

  • Runnable Example

    DECLARE @tbl AS TABLE (Seq int, Security varchar(3), Quantity int, Price int, Consid int) ;
INSERT INTO @tbl VALUES
(1, 'IBM', 1000, 20, -20000)
,(2, 'IBM', 2000, 22, -44000)
,(3, 'IBM', -1000, 30, 30000)
,(4, 'IBM', -2000, 20, 40000)
,(5, 'IBM', -2000, 20, -20000);

WITH RunningInventory AS (
SELECT l.Seq, SUM(r.Quantity) AS Inv
FROM @tbl AS l
LEFT JOIN @tbl r
ON r.Seq <= l.Seq
GROUP BY l.Seq
)
SELECT *
FROM @tbl AS trx
INNER JOIN RunningInventory
    ON trx.Seq = RunningInventory.Seq
WHERE RunningInventory.Inv >= 0 ;