diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 78 |
1 files changed, 38 insertions, 40 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 6703c8e2..19a5c224 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -3078,10 +3078,10 @@ CREATE OR REPLACE VIEW tranche_settlements AS SELECT WHERE orig_attach IS NOT NULL; -CREATE OR REPLACE VIEW tranchepayment_settlements AS SELECT tc.tranche_id::TEXT AS id, tc.date AS settle_date, - cds.fund::fund, cds.cp_code::text, 'TRANCHE_CF' AS asset_class, - 'OTC'::text AS account, tc.currency::currency, - (COALESCE(tc.principal,0) + COALESCE(tc.accrued,0))::numeric(11,2) AS payment_amount +CREATE OR REPLACE VIEW tranchepayment_settlements AS SELECT tc.tranche_id::TEXT AS id, tc.date AS settle_date, + cds.fund::fund, cds.cp_code::text, 'TRANCHE_CF' AS asset_class, + 'OTC'::text AS account, tc.currency::currency, + (COALESCE(tc.principal,0) + COALESCE(tc.accrued,0))::numeric(11,2) AS payment_amount FROM tranche_cashflows tc LEFT JOIN cds ON tc.tranche_id=cds.id ; @@ -3160,9 +3160,9 @@ AS SELECT t.settle_date, SELECT * FROM spot_settlements UNION SELECT * FROM fxswap_settlements - UNION - SELECT * FROM trs_settlements - UNION + UNION + SELECT * FROM trs_settlements + UNION SELECT * FROM equityoption_settlements) t LEFT JOIN counterparties c on t.cp_code=c.code GROUP BY t.settle_date, t.fund, c.name, t.cp_code, t.asset_class, t.currency, t.account @@ -3276,7 +3276,7 @@ SELECT eo.id, eo.strike, eo.buysell, eo.quantity * eo.underlyingshares * price * (2* buysell::integer -1.) - eo.underlyingshares + eo.underlyingshares FROM equityoptions eo JOIN counterparties cps ON cp_code = code) ORDER BY trade_date DESC; CREATE TABLE bond_tickets ( bbg_ticket_id text NOT NULL, @@ -4736,11 +4736,11 @@ SELECT id, FROM irs JOIN counterparties ON cp_code = code) ORDER BY trade_date DESC, folder; CREATE OR REPLACE FUNCTION list_iam(p_date date, p_fund fund) RETURNS TABLE (trade_date date, broker text, clean_folder strategy, start_money numeric(11,2), currency currency, fund fund, folder text, portfolio portfolio) AS $$ BEGIN RETURN QUERY -SELECT si.date as trade_date, si.broker, si.strategy as clean_strat, si.amount::NUMERIC(11,2), -si.currency, si.fund, COALESCE(pfm.folder::text, strategy::text) AS folder, pfm.portfolio -FROM (SELECT *, rank() OVER(PARTITION BY si.broker,si.fund ORDER BY date desc) -FROM strategy_im si WHERE si.fund=p_fund AND si.date<=p_date ORDER BY date DESC) si -LEFT JOIN portfolio_folder_mapping pfm ON pfm.clean_folder=si.strategy::TEXT +SELECT si.date as trade_date, si.broker, si.strategy as clean_strat, si.amount::NUMERIC(11,2), +si.currency, si.fund, COALESCE(pfm.folder::text, strategy::text) AS folder, pfm.portfolio +FROM (SELECT *, rank() OVER(PARTITION BY si.broker,si.fund ORDER BY date desc) +FROM strategy_im si WHERE si.fund=p_fund AND si.date<=p_date ORDER BY date DESC) si +LEFT JOIN portfolio_folder_mapping pfm ON pfm.clean_folder=si.strategy::TEXT WHERE RANK=1 and abs(amount) >= .01; END $$ LANGUAGE plpgsql; CREATE TYPE custodian AS ENUM('BNY', 'UMB', 'NT', "SCOTIA"); @@ -4807,17 +4807,17 @@ 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 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 +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') +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 @@ -4863,35 +4863,33 @@ CREATE TABLE citco_account_mapping (fund fund, citco_code text, account TEXT ref CREATE OR REPLACE VIEW citco_cash_valuations AS -SELECT - cr.period_end_date AS periodenddate, - cr.fund, - cr.trade_ccy AS invccy, - cr.trade_ccy AS invid, - sum(cr."quantity(end)") AS endlocalmv, - 'CASH' AS port, - 'CSH_CASH' AS strat, +SELECT + cr.period_end_date AS periodenddate, + cr.fund, + cr.trade_ccy AS invccy, + cr.trade_ccy AS invid, + sum(cr."quantity(end)") AS endlocalmv, + 'CASH' AS port, + 'CSH_CASH' AS strat, cr."prime_broker/clearing_broker" AS custacctname FROM citco_reports cr -GROUP BY - cr.period_end_date, - cr.fund, - cr.symbol, - cr.trade_ccy, +GROUP BY + cr.period_end_date, + cr.fund, + cr.symbol, + cr.trade_ccy, cr."prime_broker/clearing_broker" -HAVING +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", +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; - - |