diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 26 |
1 files changed, 14 insertions, 12 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 53746434..d56f7120 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -4923,13 +4923,13 @@ AS SELECT cr.period_end_date AS periodenddate, cr.fund, - cr.symbol AS invid, + cr.trade_ccy AS invid, cr.trade_ccy::currency AS invccy, 'CASH'::text AS port, 'CSH_CASH'::text AS strat, cr."prime_broker/clearing_broker" AS custacctname, 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) + 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 @@ -4992,16 +4992,18 @@ HAVING cr.symbol IN ('EUR Cash Balance', 'USD Cash Balance'); -CREATE OR REPLACE VIEW citco_otc_valuations AS SELECT period_end_date, fund, "prime_broker/clearing_broker", - COALESCE(split_part("strategy", '/', 1), 'CASH') AS portfolio, - COALESCE(split_part("strategy", '/', 2), 'CSH_CASH') AS strategy, - sum(issue_market_value) -FROM citco_reports cr -WHERE trade_ccy IN ('USD', 'EUR') - AND "prime_broker/clearing_broker" LIKE '%_IS' -GROUP BY period_end_date, fund, "prime_broker/clearing_broker", - COALESCE (split_part("strategy", '/', 1), 'CASH'), - COALESCE (split_part("strategy", '/', 2), 'CSH_CASH') ORDER BY period_end_date desc; +CREATE OR REPLACE VIEW citco_otc_valuations +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, + sum(cr.issue_nav_contribution) AS endlocalmv + FROM citco_reports cr + WHERE cr."prime_broker/clearing_broker" ~~ '%_IS'::text + 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, cr.fund; CREATE VIEW cds_termination_mapping AS SELECT t.id AS termination_id, cds.* |