diff options
| -rw-r--r-- | sql/dawn.sql | 22 |
1 files changed, 18 insertions, 4 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 94e32483..144d82a7 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -4852,11 +4852,25 @@ CREATE TABLE irs_tickets ( ); -CREATE OR REPLACE VIEW admin_forward_valuations AS -SELECT invccy, invid, periodenddate, fund, port, strat, sum(endlocalmv) as endlocalmv +CREATE OR REPLACE VIEW globeop_forward_valuations AS +SELECT periodenddate, fund, invccy, invid, port, strat, sum(endlocalmv) as endlocalmv FROM valuation_reports vr GROUP BY invccy, invid, periodenddate, fund, port, strat -HAVING invid IN ('EURF', 'USDF'); +HAVING invid IN ('EURF') ORDER BY periodenddate desc, fund; + +CREATE OR REPLACE VIEW citco_forward_valuation +AS SELECT + cr.period_end_date AS periodenddate, + cr.fund, + "substring"(cr.bloomberg_code, 0, 4) AS invccy, + "substring"(cr.bloomberg_code, 0, 4) || 'F'::text AS invid, + split_part(cr.strategy, '/'::text, 1) AS port, + split_part(cr.strategy, '/'::text, 2) AS strat, + sum(cr."quantity(end)") AS endlocalmv + FROM citco_reports cr + GROUP BY cr.fund, cr.bloomberg_code, cr.period_end_date, cr.strategy + HAVING cr.bloomberg_code ~~ '%CURNCY'::text + ORDER BY cr.period_end_date DESC, fund; CREATE OR REPLACE VIEW globeop_otc_valuations AS SELECT periodenddate, fund, invccy, custacctname, port, strat, sum( @@ -4871,7 +4885,7 @@ 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) ORDER BY periodenddate desc, fund; -CREATE OR REPLACE VIEW public.citco_otc_valuations +CREATE OR REPLACE VIEW 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, |