diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 59 |
1 files changed, 58 insertions, 1 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 5b79b887..2a35bfc8 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -4717,4 +4717,61 @@ CREATE TABLE irs_tickets ( cclear_usiuti text NULL, account text NULL, client_fcm text NULL -);
\ No newline at end of file +); + + +CREATE OR REPLACE VIEW admin_forward_valuations AS +SELECT invccy, invid, periodenddate, fund, port, strat, sum(endlocalmv) as endlocalmv +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, + sum( + CASE + WHEN endbookmv = 0 OR endlocalmv = 0 THEN 0 + ELSE endbooknav / (endbookmv / endlocalmv) + END + ) as endlocalmv +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); + +CREATE OR REPLACE VIEW public.admin_cash_valuations +AS SELECT valuation_reports.invid, + valuation_reports.invccy, + valuation_reports.fund, + valuation_reports.port, + valuation_reports.strat, + valuation_reports.periodenddate, + valuation_reports.custacctname, + sum(valuation_reports.endqty) AS endlocalmv + FROM valuation_reports + GROUP BY valuation_reports.invccy, valuation_reports.invid, valuation_reports.fund, valuation_reports.port, valuation_reports.strat, valuation_reports.periodenddate, valuation_reports.custacctname + HAVING valuation_reports.invid = ANY (ARRAY['EUR'::text, 'USD'::text]); + + +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 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 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 admin_cash_valuations + WHERE invid = 'EUR' + GROUP BY periodenddate, fund, port +) c +USING (periodenddate, fund, port); |