-- Hint: use Ctrl+Enter for SQL autocomplete
CREATE TABLE currencies (
id CHAR(3) PRIMARY KEY,
ndate DATE,
rate DECIMAL(12,6)
);
INSERT INTO currencies VALUES ('RUB','2016-12-31', 1), ('USD','2016-12-31', 75.64), ('EUR','2016-12-31', 91.67);
CREATE TABLE clients (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255)
);
INSERT INTO clients (name) VALUES ('CLient 1'), ('CLient 2');
CREATE TABLE deposits (
num INT PRIMARY KEY,
ndate DATE,
client_id INT,
balance DECIMAL(12,2),
currency CHAR(3)
);
INSERT INTO deposits VALUES (342344,'2017-01-01', 1, 100, 'RUB'), (342345, 1, 100, 'USD'), (342346, 2, 100, 'EUR');
SELECT deposits.ndate, deposits.balance*CASE WHEN DAYOFWEEK(deposits.ndate) IN (1, 7)
THEN (SELECT rate FROM currencies WHERE ecurrencies.currency = ‘USD’ AND currencies.ndate = (SELECT MAX (ndate) FROM currencies WHERE ndate < deposits.ndate)) ELSE ( SELECT rate FROM currencies WHERE currencies.currency = ‘USD’ AND currencies.ndate = deposits.ndate)
END) AS sum_in_rub
FROM deposits
WHERE deposits.currency = ‘USD’
Telegram: DB developers channel