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:
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?
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