Рассчитать результаты методом LIFO через TSQL
Я хочу сделать расчет с помощью метода LIFO (последний пришел первым вышел) с TSQL.
Использование метода LIFO потребует от вас расчета прибыли / убытка за счет продажи последних транзакций.
Пример того, как это работает:
- сделка заключена 1 марта, ПОКУПКА 10 акций по 5 долларов каждая
- сделка заключена 2 марта, ПОКУПКА 15 акций по 6 долларов каждая
- сделка заключена 3 марта, ПОКУПКА 5 акций по 4 доллара каждая
- сделка заключена 4 марта, мы ПРОДАЕМ 17 акций по 7 долларов каждая
По четвертой сделке мы продали 5 акций с 3 марта по 4 доллара каждая и 12 акций со 2 марта по 6 долларов каждая.
Итак, теперь мы оставили следующее: 10 акций по 5 долларов каждая от сделки на 1 марта 3 акции от 6 долларов каждая от сделки 2 марта (17-5-15 = -3).
Если осталось 13 акций со средней ценой (10*5 + 3*6) / 13 = 5,23076923
Вот сценарии генерации тестовых данных:
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)
Я хотел бы рассчитать финансовую позицию и многие другие параметры, которые потребовали бы, чтобы я знал, сколько осталось акций с соответствующей ценой. Я дошел до этого до сих пор:
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
Выход:
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
Желаемый результат:
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
Каков наилучший путь?
2 ответа
Я подозреваю, что вы, вероятно, захотите чередовать ваши покупки и продажи, поэтому, хотя я думаю, что другой ответ был отличной отправной точкой, он не вполне справится со всем сценарием.
В принципе, я думаю, вам придется справиться с этим с помощью какого-то итерационного механизма. Я попытался сделать это с помощью рекурсии, но, к сожалению, аналитические функции просто не работают с этим методом. Таким образом, я вернулся к временному столу и циклу while.
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
Я урезал это для публикации. Посмотрите это в действии здесь с немного большим количеством вывода, чтобы лучше следовать логике: http://rextester.com/WPLKLJ95730
Поскольку ваш пример довольно узок, будет трудно составить пуленепробиваемое решение. Но это должно заставить вас начать на правильном пути, или, по крайней мере, трек. Он использует вид обратного промежуточного итога и затем вычитает из суммы запаса. Используя ваш набор данных с небольшим изменением:
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