diff options
| -rw-r--r-- | sql/dawn.sql | 42 |
1 files changed, 29 insertions, 13 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 207f6bdf..53746434 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -4890,7 +4890,7 @@ HAVING invccy IN ('EUR', 'USD') AND custacctname IN (SELECT cash_account FROM accounts2 WHERE account_type = 'Cash' AND active) ORDER BY periodenddate desc, fund; CREATE OR REPLACE VIEW citco_otc_valuations -AS SELECT cr.period_end_date as periodenddate, cr.fund, cr.trade_ccy AS invccy, +AS SELECT cr.period_end_date as periodenddate, cr.fund, cr.trade_ccy::currency AS invccy, cr."prime_broker/clearing_broker" AS custacctname, COALESCE(split_part(cr.strategy, '/'::text, 1), 'CASH'::text) AS port, COALESCE(split_part(cr.strategy, '/'::text, 2), 'CSH_CASH'::text) AS strat, @@ -4900,14 +4900,18 @@ AS SELECT cr.period_end_date as periodenddate, cr.fund, cr.trade_ccy AS invccy, GROUP BY cr.trade_ccy, cr.period_end_date, cr.fund, cr."prime_broker/clearing_broker", (COALESCE(split_part(cr.strategy, '/'::text, 1), 'CASH'::text)), (COALESCE(split_part(cr.strategy, '/'::text, 2), 'CSH_CASH'::text)) ORDER BY cr.period_end_date DESC, fund; +CREATE OR REPLACE VIEW admin_otc_valuations +AS SELECT * FROM globeop_otc_valuations UNION SELECT * FROM citco_otc_valuations ORDER BY periodenddate desc, fund; + + CREATE OR REPLACE VIEW globeop_cash_valuations AS SELECT vr.periodenddate, vr.fund, vr.invid, vr.invccy, - vr.port, - vr.strat, + vr.port::text, + vr.strat::text, vr.custacctname, sum(vr.endqty) AS endlocalmv FROM valuation_reports vr @@ -4915,40 +4919,52 @@ AS SELECT HAVING vr.invid = ANY (ARRAY['EUR'::text, 'USD'::text]) ORDER BY periodenddate DESC, fund; CREATE OR REPLACE VIEW citco_cash_valuations -AS SELECT cr.period_end_date AS periodenddate, +AS +SELECT + cr.period_end_date AS periodenddate, cr.fund, cr.symbol AS invid, - cr.trade_ccy AS invccy, + cr.trade_ccy::currency AS invccy, 'CASH'::text AS port, 'CSH_CASH'::text AS strat, cr."prime_broker/clearing_broker" AS custacctname, - sum(cr."quantity(end)") AS endlocalmv - FROM citco_reports cr - GROUP BY cr.period_end_date, cr.fund, cr.symbol, cr.trade_ccy, cr."prime_broker/clearing_broker" - HAVING cr.symbol = ANY (ARRAY['EUR Cash Balance'::text, 'USD Cash Balance'::text]) ORDER BY period_end_date DESC, fund; + CASE WHEN cr."prime_broker/clearing_broker" = 'BOA_FC' + THEN (SELECT sum(cr."quantity(end)")-(account_value_market- ending_balance) FROM fcm_moneyline fm WHERE account='6MZ20K79'AND "currency" = 'EUR' AND date=cr.period_end_date) + ELSE sum(cr."quantity(end)") + END AS endlocalmv +FROM citco_reports cr +GROUP BY cr.period_end_date, cr.fund, cr.symbol, cr.trade_ccy, cr."prime_broker/clearing_broker" +HAVING cr.symbol = ANY (ARRAY['EUR Cash Balance'::text, 'USD Cash Balance'::text]) +ORDER BY period_end_date DESC, fund; + + +CREATE OR REPLACE VIEW admin_cash_valuations +AS SELECT * FROM globeop_cash_valuations UNION SELECT * FROM citco_cash_valuations ORDER BY periodenddate desc, fund; CREATE VIEW eur_fx_valuations AS SELECT periodenddate, fund, port, forward_valuations, otc_valuations, cash_valuations FROM ( SELECT periodenddate, fund, port, sum(endlocalmv) AS forward_valuations - FROM globeop_forward_valuations + FROM admin_forward_valuations WHERE invid = 'EURF' GROUP BY periodenddate, fund, port ) f FULL OUTER JOIN ( SELECT periodenddate, fund, port, sum(endlocalmv) AS otc_valuations - FROM globeop_otc_valuations + FROM admin_otc_valuations WHERE invccy = 'EUR' GROUP BY periodenddate, fund, port ) otc USING (periodenddate, fund, port) FULL OUTER JOIN ( SELECT periodenddate, fund, port, sum(endlocalmv) AS cash_valuations - FROM globeop_cash_valuations UNION citco_cash_valuations + FROM admin_cash_valuations WHERE invid = 'EUR' GROUP BY periodenddate, fund, port ) c -USING (periodenddate, fund, port); +USING (periodenddate, fund, port) +ORDER BY periodenddate DESC, fund; + CREATE VIEW eur_fx_excess AS SELECT fund, periodenddate, sum(COALESCE (forward_valuations,0) + COALESCE (otc_valuations,0) + COALESCE (cash_valuations,0)) AS eur_excess FROM eur_fx_valuations GROUP BY fund, periodenddate ORDER BY periodenddate DESC; |