Calculate results with LIFO method via TSQL

Question

I want to make calculation via LIFO (last in first out) method with TSQL.

Using LIFO method will require you to calculate profit/loss by selling last transactions.

Example how it works:

1. deal is concluded on 1 march we BUY 10 stocks for 5 dollars each
2. deal is concluded on 2 march we BUY 15 stocks for 6 dollars each
3. deal is concluded on 3 march we BUY 5 stocks for 4 dollars each
4. deal is concluded on 4 march we SELL 17 stocks for 7 dollars each

By the 4th transaction we now have sold 5 stocks from 3rd march for 4 dollars each and 12 stocks from 2nd march 6 dollars each.

So now we have left the following: 10 stocks for 5 dollars each from transaction on 1 march 3 stocks from 6 dollars each from transaction on 2 march (17-5-15 = -3).

Having 13 stocks left with an average price of (10*5 + 3*6) / 13 = 5.23076923

Here's test data generation scripts:

use TestTask
go
IF OBJECT_ID('testtable','U')IS NOT NULL
        DROP TABLE testtable
go
create table testtable
(
stockid int not null,
dealid int identity (1,1) not null,
dealtype char(1) not null,
stockdate datetime not null,
stockamount int not null,
priceperstock int not null
)
insert into testtable(stockid,dealtype,stockdate,stockamount,priceperstock)
VALUES
    (111,'B','01.03.2016',10,5),
    (111,'B','02.03.2016',15,6),
    (111,'B','03.03.2016',5,4),
    (111,'S','04.03.2016',17,7)

I would like to calculate finance position and many other parameters which would requre me to know how many stocks with appropriate price left. I have come to this so far:

select
stockid,
dealid,
dealtype,
stockdate,
priceperstock,
case dealtype
    when 'B' then stockamount
    when 'S' then -stockamount
end as stockamount,
sum(
        case dealtype
            when 'B' then stockamount
            when 'S' then -stockamount
        end
    ) over (partition by 
        stockid order by dealid ROWS UNBOUNDED PRECEDING)
         as poistion
from testtable

Output:

stockid dealid  dealtype       stockdate           priceperstock    stockamount    poistion
    111       1       B       2016-01-03 00:00:00.000      5             10             10
    111       2       B       2016-02-03 00:00:00.000      6             15             25
    111       3       B       2016-03-03 00:00:00.000      4             5              30
    111       4       S       2016-04-03 00:00:00.000      7            -17             13

Desired output:

stockid dealid  dealtype       stockdate           priceperstock    stockamount    poistion    stocksleft
    111       1       B       2016-01-03 00:00:00.000      5             10             10      10
    111       2       B       2016-02-03 00:00:00.000      6             15             25      3
    111       3       B       2016-03-03 00:00:00.000      4             5              30      0
    111       4       S       2016-04-03 00:00:00.000      7            -17             13      0

What is the best way to go?

Answer 1

I suspect that you'll probably be wanting to interleave your buys and sells so while I think the other answer was a great starting point, it won't quite handle the entire scenario.

Basically I think you will have to handle this with some kind of iterative mechanism. I tried to do it with recursion but unfortunately the analytic functions just don't work right with that method. So I fell back to a temp table and while loop.

create table #R (
    lvl int not null, stockId int not null, dealId int not null,
    stockDate datetime not null, stockAmount int not null, pricePerStock int not null,
    stockRemaining int not null, amountDeducted int not null
);

insert into #R (
    lvl, stockId, dealId, stockDate, stockAmount,
    pricePerStock, stockRemaining, amountDeducted
)
select 0, stockId, dealId, stockDate, stockAmount, pricePerStock, stockAmount, 0
from <T> where dealtype = 'B' /* <--- your table is <T> */

declare @lvl int = 0;
declare @rowCount int = 1;
while @rowCount > 0
begin
    set @lvl = @lvl + 1;
    with sells as (
        select stockId, dealId as saleId,
            row_number() over (order by dealId) as sellNum, stockAmount as sellAmount
        from <T> where dealType = 'S'
    )
    update #R
    set stockRemaining = (
        select stockRemaining
        from (
            select dealId,
                case
                    when r.stockRemaining + s.sellAmount
                          < sum(stockRemaining) over (order by dealId desc)
                        then r.stockRemaining
                    when sum(stockRemaining) over (order by dealId desc)
                          < s.sellAmount
                        then 0
                    else sum(stockRemaining) over (order by dealId desc)
                          - s.sellAmount
                end as stockremaining
            from sells s inner join #R r
                on r.stockId = s.stockId and r.dealId < s.saleId
            where s.stockId = #R.stockId and s.sellNum = @lvl
        ) data
        where dealId = #R.dealId
    )
    where dealId < (select saleId from sells where sellNum = @lvl);
    set @rowCount = @@rowCount;
end

I've trimmed it down for posting. See it in action here with a little more output to follow the logic better: http://rextester.com/WPLKLJ95730

Answer 2

Since your example is pretty narrow, it will be hard to put together a bulletproof solution. But this should get you started on the right track, or at least a track. It uses a kind of reverse running total and then subtracts from the stock amount. Using your data set with a small change:

create table #testtable
(
stockid int not null,
dealid int identity (1,1) not null,
dealtype char(1) not null,
stockdate datetime not null,
stockamount int not null,
priceperstock int not null
)
 insert into #testtable(stockid,dealtype,stockdate,stockamount,priceperstock)
    VALUES
        (111,'B','01.03.2016',10,5),
        (111,'B','02.03.2016',15,6),
        (111,'B','03.03.2016',5,4),
        (111,'S','04.03.2016',-17,7) --signed int

----Add this to see another level 
 --insert into #testtable(stockid,dealtype,stockdate,stockamount,priceperstock)
 --   VALUES
 --       (111,'S','05.03.2016',-12,5)
    ;WITH CTE
    AS (
        SELECT stockid
            , dealid
            , dealtype
            , stockdate
            , priceperstock
            , stockamount
            , sum(stockamount) OVER (
                ORDER BY dealid DESC
                ) AS runningtotal
            , sum(stockamount) OVER (
            ORDER BY dealid) AS position
        FROM #testtable
        )
    SELECT stockid
        , dealid
        , dealtype
        , stockdate
        , priceperstock
        , stockamount
        --, runningtotal
        , position
        , CASE 
            WHEN dealtype = 'S' 
                THEN 0
            WHEN stockamount > runningtotal AND runningtotal < 0
                THEN 0
            WHEN stockamount > runningtotal AND runningtotal >= 0
                THEN runningtotal
            WHEN stockamount < runningtotal
                THEN stockamount
            END AS StockRemaining
    FROM cte
    ORDER BY dealid