sql server - FIFO Profit Calculation in T-SQL

Question

I need some help or pointers with a single query, that can build a profit/loss for each individual stock trade based on FIFO (first-in-first-out) principle on the dataset attached below. Should be compatible with SQL Server 2016+ and Azure SQL.

Example data looks like this:

StockName	TransactionDate	TranCode	Quantity	PriceUSD	TotalAmountUSD
StockABC	2017-12-11 11:16:11.000	BUY	2364,444444	0,114323	270,310382
StockXYZ	2017-12-11 11:16:11.000	BUY	2364,444444	0,114323	270,310382
StockABC	2017-12-14 14:16:24.000	SELL	1000	0,158849	158,849
StockXYZ	2017-12-14 14:16:24.000	SELL	1000	0,158849	158,849
StockABC	2017-12-14 19:38:46.000	SELL	700	0,198934	139,2538
StockXYZ	2017-12-14 19:38:46.000	SELL	700	0,198934	139,2538
StockABC	2017-12-15 09:38:09.000	SELL	664,4444444	0,207171	137,65362

question from:https://stackoverflow.com/questions/65939704/fifo-profit-calculation-in-t-sql

Answer 1

Here's a simple way (sort of) which uses a tally function called dbo.fnTally to generate 1 row for each item or "share" of the Quantity. Both 'buy_cte' and 'sell_cte' uses CROSS APPLY dbo.fnTally(1, s.Quantity) to expand the rows for each item of quantity. Also both CTE's assign an ordinal row_number called 'trans_rn'. By joining the two CTE's on 'trans_rn' the FIFO profitability can be calculated by just summing the prices. Something like this

dbo.fnTally

CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
    Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
**********************************************************************************************************************/
        (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  H2(N) AS ( SELECT 1 
               FROM (VALUES
                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    )V(N))            --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
            SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
            SELECT TOP(@MaxN)
                   N = ROW_NUMBER() OVER (ORDER BY N)
              FROM H8;

;with 
buy_cte(Currency, TransactionDate, Price, trans_rn) as (
    select Currency, TransactionDate, Price,
           row_number() over (partition by Currency order by TransactionDate)
    from #Stock s
         cross apply dbo.fnTally(1, s.Quantity) fn
    where TranCode='BUY'),
sell_cte(Currency, TransactionDate, Price, trans_rn) as (
    select Currency, TransactionDate, Price,
           row_number() over (partition by Currency order by TransactionDate)
    from #Stock s
         cross apply dbo.fnTally(1, s.Quantity) fn
    where TranCode='SELL')
select s.Currency, s.TransactionDate, 
       cast(sum(b.price) as decimal(14,2)) buy_sum, 
       cast(sum(s.price) as decimal(14,2)) sell_sum, 
       cast(sum(s.price-b.price) as decimal(14,2)) profit_sum, 
       count(*) q_sold
from buy_cte b
     join sell_cte s on b.Currency=s.Currency
                    and b.trans_rn=s.trans_rn
                    and b.TransactionDate<s.TransactionDate
group by s.Currency, s.TransactionDate
order by TransactionDate;

Currency    TransactionDate         buy_sum sell_sum    profit_sum  q_sold
StockABC    2017-12-14 14:16:00.000 114.32  158.85      44.53       1000
StockABC    2017-12-14 19:38:00.000 80.03   139.25      59.23       700
StockABC    2017-12-15 09:38:00.000 75.91   137.56      61.65       664
StockABC    2017-12-21 21:02:00.000 163.50  158.24      -5.26       334
StockABC    2017-12-26 10:45:00.000 195.32  174.92      -20.40      399
StockABC    2017-12-30 11:34:00.000 244.76  316.88      72.12       500
StockABC    2018-01-03 17:45:00.000 9.79    21.13       11.34       20
StockABC    2018-03-21 15:42:00.000 633.45  319.92      -313.53     1472
StockABC    2018-04-16 07:53:00.000 73.92   105.97      32.05       450
StockABC    2018-04-24 20:15:00.000 75.44   136.96      61.52       460
StockABC    2018-07-22 17:08:00.000 66.53   87.15       20.62       500
StockABC    2018-08-05 17:34:00.000 133.00  127.40      -5.60       1000