diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 15 |
1 files changed, 13 insertions, 2 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 661b6126..94e32483 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -4858,7 +4858,7 @@ FROM valuation_reports vr GROUP BY invccy, invid, periodenddate, fund, port, strat HAVING invid IN ('EURF', 'USDF'); -CREATE OR REPLACE VIEW admin_otc_valuations AS SELECT invccy, custacctname, port, strat, periodenddate, fund, +CREATE OR REPLACE VIEW globeop_otc_valuations AS SELECT periodenddate, fund, invccy, custacctname, port, strat, sum( CASE WHEN endbookmv = 0 OR endlocalmv = 0 THEN 0 @@ -4869,7 +4869,18 @@ FROM valuation_reports vr WHERE invid NOT IN ('EUR', 'EURF') AND endbooknav IS NOT NULL GROUP BY invccy, custacctname, port, strat, periodenddate, fund HAVING invccy IN ('EUR', 'USD') -AND custacctname IN (SELECT cash_account FROM accounts2 WHERE account_type = 'Cash' AND active); +AND custacctname IN (SELECT cash_account FROM accounts2 WHERE account_type = 'Cash' AND active) ORDER BY periodenddate desc, fund; + +CREATE OR REPLACE VIEW public.citco_otc_valuations +AS SELECT cr.period_end_date as periodenddate, cr.fund, cr.trade_ccy 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_market_value) 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, fund; CREATE OR REPLACE VIEW public.admin_cash_valuations AS SELECT valuation_reports.invid, |