0

I have shares transactions and I need to get out pairs of buys and sells with FIFO principle.

If this is starting point:

For starter we take this

Then this is result:

enter image description here

Based on this question FIFO Profit Calculation in T-SQL I tried to implement solution. (you will find tally function in linked question)

CREATE TABLE #Shares
(
    ID int IDENTITY(1,1),
    BuySell varchar(10), 
    DateT DATE,
    TypeT varchar(3),
    Quantity decimal(15, 4),
    ValueT decimal(15, 4)
)

INSERT INTO #Shares(BuySell, DateT, TypeT, Quantity, ValueT)
     VALUES ('B1', '2020-11-27', 'B', 10, 100) 
          , ('B2', '2020-11-28', 'B', 20, 70) 
          , ('B3', '2020-11-29', 'B', 30, 120) 
          , ('S1', '2020-11-30', 'S', 50, 90) 
          , ('B4', '2020-12-12', 'B', 10, 100) 
          , ('S2', '2020-12-16', 'S', 20, 95) 

/* FIFO */
;with buy_cte(BuySell, DateT, ValueT, rownum) as
(
select BuySell,
       DateT, 
       ValueT,
       row_number() over (order by DateT)
  from #Shares p
 cross apply dbo.fnTally(1, p.Quantity) fn
 where TypeT = 'B'
 )

, sell_cte(BuySell, DateT, ValueT, rownum) as
(
select BuySell,
       DateT, 
       ValueT,
       row_number() over (order by DateT)
  from #Shares o
 cross apply dbo.fnTally(1, o.Quantity) fn
 where TypeT = 'S'
 )

select concat(p.BuySell, '+', o.BuySell) as Pairs
     , p.BuySell as Buy
     , o.BuySell as Sell
     , count(*) as SellQuantity
     , o.ValueT 
  from sell_cte o
 inner join buy_cte p 
    on o.rownum = p.rownum
   and o.DateT >= p.DateT
 group by p.BuySell, o.BuySell, o.ValueT 
 order by p.BuySell, o.BuySell

And it works fine.

But this works for integer quantities only and I have quatities like this: 0.00090000, 0.00040000, 0.00020000, 1.01280000, ...

My question is how to get same result as described before when quantity is integer, but this time with decimals for quantity.

To just multiplay decimals to get integers will not do the trick since then you end up with millions of records in ctes. Shares table can have hundreds of tousands of records, so cursors will not do either...

Most of the similar problems are solved with tally table, but that is no good for decimals.

Roman
  • 13
  • 5

0 Answers0