Created
September 10, 2018 13:03
-
-
Save Griminy/487217c0d8f0e90289ffd50e09633247 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| "SELECT *, (fund_value / COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'RUB' \n ORDER BY date DESC LIMIT 1), 1)) as fund_value_converted, (sumall / COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'RUB' \n ORDER BY date DESC LIMIT 1), 1)) as sumall_converted, (mark / COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'RUB' \n ORDER BY date DESC LIMIT 1), 1)) as mark_converted, (tax / COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'RUB' \n ORDER BY date DESC LIMIT 1), 1)) as tax_converted, (operation_sum / COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'RUB' \n ORDER BY date DESC LIMIT 1), 1)) as operation_sum_converted, (cost / COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'RUB' \n ORDER BY date DESC LIMIT 1), 1)) as cost_converted, (mark / COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'RUB' \n ORDER BY date DESC LIMIT 1), 1)) as mark_converted, (fund_value / COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'RUB' \n ORDER BY date DESC LIMIT 1), 1)) as fund_value_converted FROM \"pifs_transactions\" WHERE \"pifs_transactions\".\"fund_code\" = '5' AND \"pifs_transactions\".\"fund_code\" = '5' AND \"pifs_transactions\".\"user_code\" = '00002925' AND \"pifs_transactions\".\"action_type\" = 1 ORDER BY \"pifs_transactions\".\"date\" ASC UNION ALL SELECT *, (fund_value / COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'RUB' \n ORDER BY date DESC LIMIT 1), 1))*(COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'USD' \n ORDER BY date DESC LIMIT 1), 1)) as fund_value_converted, (sumall / COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'RUB' \n ORDER BY date DESC LIMIT 1), 1))*(COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'USD' \n ORDER BY date DESC LIMIT 1), 1)) as sumall_converted, (mark / COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'RUB' \n ORDER BY date DESC LIMIT 1), 1))*(COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'USD' \n ORDER BY date DESC LIMIT 1), 1)) as mark_converted, (tax / COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'RUB' \n ORDER BY date DESC LIMIT 1), 1))*(COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'USD' \n ORDER BY date DESC LIMIT 1), 1)) as tax_converted, (operation_sum / COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'RUB' \n ORDER BY date DESC LIMIT 1), 1))*(COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'USD' \n ORDER BY date DESC LIMIT 1), 1)) as operation_sum_converted, (cost / COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'RUB' \n ORDER BY date DESC LIMIT 1), 1))*(COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'USD' \n ORDER BY date DESC LIMIT 1), 1)) as cost_converted, (mark / COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'RUB' \n ORDER BY date DESC LIMIT 1), 1))*(COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'USD' \n ORDER BY date DESC LIMIT 1), 1)) as mark_converted, (fund_value / COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'RUB' \n ORDER BY date DESC LIMIT 1), 1))*(COALESCE((SELECT value FROM currency_rates \n WHERE currency_rates.date <= pifs_transactions.date AND \n currency_rates.curr_code = 'USD' \n ORDER BY date DESC LIMIT 1), 1)) as fund_value_converted FROM \"pifs_transactions\" WHERE \"pifs_transactions\".\"fund_code\" = '9' AND \"pifs_transactions\".\"fund_code\" = '9' AND \"pifs_transactions\".\"user_code\" = '00002925' AND \"pifs_transactions\".\"action_type\" = 1 ORDER BY \"pifs_transactions\".\"date\" ASC" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment