Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE transactions (
- id INT PRIMARY KEY AUTO_INCREMENT,
- code VARCHAR(10),
- date DATETIME,
- mode ENUM('Buy', 'Sell', 'Count', 'Return'),
- quantity INT,
- price DECIMAL(10,2),
- price_currency ENUM('ARS', 'USD'),
- usd_to_ars DECIMAL(10,2),
- return_id INT NULL DEFAULT NULL
- )
- INSERT INTO transactions (code, date, mode, quantity, price, price_currency, usd_to_ars) VALUES ("a", "20180101", 'Buy', 4, 10, 'ARS', 3.7);
- INSERT INTO transactions (code, date, mode, quantity, price, price_currency, usd_to_ars) VALUES ("a", "20180102", 'Buy', 9, 8, 'ARS', 5.8);
- INSERT INTO transactions (code, date, mode, quantity, price, price_currency, usd_to_ars) VALUES ("a", "20180103", 'Sell', -3, 0, 'USD', 0);
- INSERT INTO transactions (code, date, mode, quantity, price, price_currency, usd_to_ars) VALUES ("b", "20180104", 'Buy', 5, 5, 'USD', 8.9);
- INSERT INTO transactions (code, date, mode, quantity, price, price_currency, usd_to_ars) VALUES ("a", "20180105", 'Buy', 2, 7, 'USD', 3.4);
- INSERT INTO transactions (code, date, mode, quantity, price, price_currency, usd_to_ars) VALUES ("b", "20180106", 'Buy', 1, 8, 'ARS', 9);
- INSERT INTO transactions (code, date, mode, quantity, price, price_currency, usd_to_ars) VALUES ("a", "20180107", 'Sell', -8, 0, 'USD', 4.4);
- INSERT INTO transactions (code, date, mode, quantity, price, price_currency, usd_to_ars) VALUES ("a", "20180108", 'Buy', 9, 9, 'ARS', 3.2);
- INSERT INTO transactions (code, date, mode, quantity, price, price_currency, usd_to_ars, return_id) VALUES ("a", "20180109", 'Return', 6, 2, 'ARS', 2, 2);
- Select * FROM (SELECT
- id, date, code, mode, quantity, price, price_currency, usd_to_ars, return_id,
- @acm := @acm + quantity as stock,
- @avr := (@avr * (@acm - quantity) + if(quantity > 0, quantity * if(mode = "Return", @avr, if(price_currency = 'USD', price, price / usd_to_ars)), quantity * @avr)) / @acm as average_price_usd
- FROM
- transactions t1,
- (SELECT @acm := 0) x,
- (SELECT @avr := 0) y) t2
- ORDER BY id DESC
- @avr := (@avr * (@acm - quantity) + if(quantity > 0, quantity * if(mode = "Return", @avr, if(price_currency = 'USD', price, price / usd_to_ars)), quantity * @avr)) / @acm as average_price_usd
- If(mode = "Return", @avr, [...])
Add Comment
Please, Sign In to add comment