А вообще у меня работает все таки не совсем так как мне надо. Если перед продажей были только покупки, то все правильно работает. Но вот если были перед продажей не только покупки, но и другие продажи, надо уже считать не сумму произведений, деленную на оставшиеся акции, а как бы обнулять некоторые продажи полностью или частично, исходя из условия, что первыми продаются самые первые купленные акции. То есть например для id = 5, на предыдущем шаге id = 4 продали 20 штук, то есть id = 1 (10 акций) и id = 2 (10 акций) обнуляюся и в расчетах не участвуют. То есть для id = 5 учитываем только покупку при id = 3:
profit = 20 * 2300 - (15 * 2150) / 15 * 20 = 3000
Причем "обнуляться могут продажи и частично" - это хорошо видно на примере id = 16
При id = 15 продали 30 штук - то есть они "обнуляют" 20 штук купленных при id = 13 и 10 штук купленных
при id = 14, то есть в расчете участвует только покупка 10 штук при id = 14: profit = * 2300 - (10 * 2100) / 10 * 5
То есть получается хоть как нужен цикл с предусловием внутри этого запроса?
Либо я просто не могу отбросить шоры процедурного мышления и понять как это сделать без циклов и ветвлений (если это вообще возможно???), помогите пожалуйста!
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
(1, '20190101', 'ivanov', 'LKOH', 10, 2000),
(2, '20190102', 'ivanov', 'LKOH', 10, 2100),
(3, '20190102', 'ivanov', 'LKOH', 15, 2150),
(4, '20190103', 'ivanov', 'LKOH', -20, 2200), profit = 2142,8571428571
(5, '20190104', 'ivanov', 'LKOH', -20, 2300),
--сейчас при id=5 profit = 0 * 2300 - (10*2000 + 10*2100 + 15 * 2150 - 20 * 2200) / 15 * 20 = 7000,
-- а должно profit = 20 * 2300 - (15 * 2150) / 15 * 20 = 3000
(6, '20190102', 'ivanov', 'GAZP', 100, 100),
(7, '20190103', 'ivanov', 'GAZP', 200, 110),
(8, '20190104', 'ivanov', 'GAZP', -50, 100),
(9, '20190105', 'ivanov', 'GAZP', -50, 90),
(10, '20190106', 'ivanov', 'GAZP', -200, 120),
(11, '20190107', 'ivanov', 'GAZP', 150, 110),
(12, '20190108', 'ivanov', 'GAZP', -100, 115),
-- id=12 В расчёте учитывается только покупка id=11, т.к. всё что ранее было куплено уже продано и на доход по этой сделке не --влияет profit = 100*115р - 100*110р = 500
(13, '20190101', 'petrov', 'LKOH', 20, 2000),
(14, '20190102', 'petrov', 'LKOH', 20, 2100),
(15, '20190103', 'petrov', 'LKOH', -30, 2200), profit = 30 * 2200 - (20 * 2000 + 20 * 2100) / 40 * 30 = 4500
(16, '20190104', 'petrov', 'LKOH', -5, 2300),
-- сейчас при id = 16 profit = 5 * 2300 - (20 * 2000 + 20 * 2100 - 30 * 2200) / 10 * 5 = 3500
-- нужно при id = 16 profit = * 2300 - (10 * 2100) / 10 * 5
(17, '20190104', 'petrov', 'LKOH', -2, 2350),
(18, '20190105', 'petrov', 'LKOH', -3, 2400),
(19, '20190106', 'petrov', 'LKOH', 5, 2000)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SELECT
ids, product, client,(ABS(qtys) * prices - (CASE WHEN (sum_qty - qty) > 0 THEN sum_profit/sum_qty ELSE price END) * ABS(qtys)) profit
FROM
(SELECT id ids, client clients, product products, qty qtys, price prices,
(SELECT SUM(qty) sum_buy FROM dbo.Deals d2 WHERE (id < d.id) AND (client = d.client) AND (product = d.product)) qty_buy
FROM dbo.Deals d
WHERE (qty < 0)) s
INNER JOIN
(SELECT id, qty, price, product, client, SUM(qty) OVER (PARTITION BY product, client ORDER BY id) sum_qty,
SUM(qty * price) OVER (PARTITION BY product, client ORDER BY id) sum_profit
FROM dbo.Deals) bs
ON bs.id = s.ids - 1
ORDER BY s.ids
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
CREATE TABLE Deals(id INT PRIMARY KEY CLUSTERED,
date_deal DATETIME,
client VARCHAR(20),
product VARCHAR(20),
qty DECIMAL(19,2),
price DECIMAL(19,7))
INSERT dbo.Deals (id, date_deal, client, product, qty, price)
VALUES (1, '20190101', 'ivanov', 'LKOH', 10, 2000),
(2, '20190102', 'ivanov', 'LKOH', 10, 2100),
(3, '20190102', 'ivanov', 'LKOH', 15, 2150),
(4, '20190103', 'ivanov', 'LKOH', -20, 2200),
(5, '20190104', 'ivanov', 'LKOH', -20, 2300),
(6, '20190102', 'ivanov', 'GAZP', 100, 100),
(7, '20190103', 'ivanov', 'GAZP', 200, 110),
(8, '20190104', 'ivanov', 'GAZP', -50, 100),
(9, '20190105', 'ivanov', 'GAZP', -50, 90),
(10, '20190106', 'ivanov', 'GAZP', -200, 120),
(11, '20190107', 'ivanov', 'GAZP', 150, 110),
(12, '20190108', 'ivanov', 'GAZP', -100, 115),
(13, '20190101', 'petrov', 'LKOH', 20, 2000),
(14, '20190102', 'petrov', 'LKOH', 20, 2100),
(15, '20190103', 'petrov', 'LKOH', -30, 2200),
(16, '20190104', 'petrov', 'LKOH', -5, 2300),
(17, '20190104', 'petrov', 'LKOH', -2, 2350),
(18, '20190105', 'petrov', 'LKOH', -3, 2400),
(19, '20190106', 'petrov', 'LKOH', 5, 2000)
|
