diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 12 |
1 files changed, 8 insertions, 4 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index b947ae13..ababbc39 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -4162,20 +4162,24 @@ SELECT id, swap_type FROM irs JOIN counterparties ON cp_code = code) ORDER BY trade_date DESC, folder; -CREATE OR REPLACE FUNCTION baml_fcm_fx(p_date date) RETURNS TABLE (account TEXT, curr currency, "TotBal" NUMERIC, "HomeCurrency" currency, "fxRate" NUMERIC, "convTotBal" NUMERIC, "BuySell" TEXT, "ValueDate" date) LANGUAGE plpgsql +CREATE OR REPLACE FUNCTION baml_fcm_fx(p_date date, p_fund fund) RETURNS TABLE (account TEXT, curr currency, "TotBal" float, "HomeCurrency" currency, "fxRate" float, "convTotBal" float, "BuySell" TEXT, "ValueDate" date) LANGUAGE plpgsql AS $function$ BEGIN - RETURN query SELECT case when a.account='V0NSCLMSPT' then '6MZ20049' else a.account end, a.curr, round(a.totbal::NUMERIC, 2) , a.homecurrency, round(a.fxrate::NUMERIC, 5), round(a.convtotbal::NUMERIC, 2), a.buysell, a.valuedate + RETURN query SELECT case when a.account='V0NSCLMSPT' then '6MZ20049' else a.account end, a.curr, a.totbal , a.homecurrency, a.fxrate, a.convtotbal, a.buysell, a.valuedate FROM(SELECT cash_account AS account, buy_currency AS curr,-buy_amount AS totbal, sell_currency AS homecurrency, spot_rate AS fxrate, sell_amount AS convtotbal, 'Buy' AS buysell, settle_date AS valuedate FROM spots WHERE sell_currency = 'USD' -AND trade_date = p_date +AND trade_date = p_date AND fund=p_fund UNION SELECT cash_account AS account, sell_currency AS curr, sell_amount AS totbal, buy_currency AS homecurrency, spot_rate AS fxrate, sell_amount AS convtotbal, 'Sell' AS buysell, settle_date AS valuedate FROM spots -WHERE buy_currency = 'USD' +WHERE buy_currency = 'USD' AND fund=p_fund AND trade_date = p_date) a WHERE a.account in ('V0NSCLMSPT', '6MZ20K79'); +<<<<<<< HEAD END; $function$ ; +======= +END; +>>>>>>> a416656e (Will do rounding within the script) |