diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 108 |
1 files changed, 54 insertions, 54 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 8550c41f..cecfbd02 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1944,39 +1944,39 @@ END; $function$ ; -CREATE VIEW ir_swap_risk_master AS SELECT - isr.date, - irs.effective_date, - irs.maturity_date, - irs.fixed_rate, - irs.fund, - irs.folder, - irs.cash_account, - irs.float_index, - irs.currency, - irs.clearing_facility, - irs.roll_day, - sum(CASE WHEN irs.payreceive THEN irs.notional ELSE -irs.notional end) AS notional, - sum(pv) AS pv, - sum("DV01") AS "DV01", - sum("IRGamma1bp") AS "IRGamma1bp" -FROM - ir_swap_risk isr -LEFT JOIN - irs ON isr.swp_id=irs.id -GROUP BY - isr.date, - irs.effective_date, - irs.maturity_date, - irs.fixed_rate, - irs.fund, - irs.folder, - irs.cash_account, - irs.float_index, - irs.currency, - irs.clearing_facility, - irs.roll_day -HAVING +CREATE VIEW ir_swap_risk_master AS SELECT + isr.date, + irs.effective_date, + irs.maturity_date, + irs.fixed_rate, + irs.fund, + irs.folder, + irs.cash_account, + irs.float_index, + irs.currency, + irs.clearing_facility, + irs.roll_day, + sum(CASE WHEN irs.payreceive THEN irs.notional ELSE -irs.notional end) AS notional, + sum(pv) AS pv, + sum("DV01") AS "DV01", + sum("IRGamma1bp") AS "IRGamma1bp" +FROM + ir_swap_risk isr +LEFT JOIN + irs ON isr.swp_id=irs.id +GROUP BY + isr.date, + irs.effective_date, + irs.maturity_date, + irs.fixed_rate, + irs.fund, + irs.folder, + irs.cash_account, + irs.float_index, + irs.currency, + irs.clearing_facility, + irs.roll_day +HAVING sum(CASE WHEN irs.payreceive THEN irs.notional ELSE -irs.notional end) !=0; @@ -4975,9 +4975,9 @@ GROUP BY periodenddate, knowledgedate, fund, invccy, invid, port, strat HAVING invid IN ('EURF') ORDER BY periodenddate desc, fund; CREATE OR REPLACE VIEW citco_forward_valuations -AS SELECT +AS SELECT cr.period_end_date AS periodenddate, - cr.knowledge_date as knowledgedate, + cr.knowledge_date as knowledgedate, cr.fund, "substring"(cr.bloomberg_code, 0, 4)::currency AS invccy, "substring"(cr.bloomberg_code, 0, 4) || 'F'::text AS invid, @@ -5018,7 +5018,7 @@ AS SELECT cr.period_end_date AS periodenddate, ORDER BY cr.period_end_date DESC, cr.fund; CREATE OR REPLACE VIEW globeop_cash_valuations -AS SELECT +AS SELECT vr.periodenddate, vr.knowledgedate, vr.fund, @@ -5033,8 +5033,8 @@ AS SELECT HAVING vr.invid = ANY (ARRAY['EUR'::text, 'USD'::text]) ORDER BY periodenddate DESC, fund; CREATE OR REPLACE VIEW citco_cash_valuations -AS -SELECT +AS +SELECT cr.period_end_date AS periodenddate, cr.knowledge_date as knowledgedate, cr.fund, @@ -5108,28 +5108,28 @@ $$ LANGUAGE plpgsql; CREATE TABLE citco_account_mapping (fund fund, citco_code text, account TEXT references accounts2(cash_account), PRIMARY KEY (fund, citco_code)); -CREATE VIEW cds_termination_mapping AS +CREATE VIEW cds_termination_mapping AS SELECT t.id AS termination_id, cds.* FROM terminations t LEFT JOIN cds ON t.deal_type = 'CDS' AND t.dealid = cds.dealid WHERE deal_type='CDS'; -CREATE VIEW swaption_termination_mapping AS +CREATE VIEW swaption_termination_mapping AS SELECT t.id AS termination_id, swaptions.* FROM terminations t LEFT JOIN swaptions ON (t.deal_type = 'CreditSwaption' OR t.deal_type = 'IRSwaption') AND t.dealid = swaptions.dealid WHERE deal_type IN ('CreditSwaption', 'IRSwaption'); -CREATE VIEW trs_termination_mapping AS +CREATE VIEW trs_termination_mapping AS SELECT t.id AS termination_id, trs.* FROM terminations t LEFT JOIN trs ON t.deal_type = 'TRS' AND t.dealid = trs.dealid WHERE deal_type='TRS'; -CREATE VIEW termination_collateral_mapping AS +CREATE VIEW termination_collateral_mapping AS SELECT * FROM (SELECT fund, folder::text, termination_id FROM cds_termination_mapping UNION SELECT fund, folder::text, termination_id FROM swaption_termination_mapping UNION -SELECT fund, folder::text, termination_id from trs_termination_mapping) a LEFT JOIN +SELECT fund, folder::text, termination_id from trs_termination_mapping) a LEFT JOIN terminations on a.termination_id = terminations.id; @@ -5147,16 +5147,16 @@ CREATE OR REPLACE FUNCTION compare_citco_bonds( AS $$ BEGIN RETURN QUERY - SELECT b.citco_security_id::text, a.identifier::text AS security_id, a.factor AS serenitas_factor, - a.notional AS serenitas_notional, b.factor AS admin_factor, - b."quantity(end)" AS admin_notional - FROM risk_positions(p_date, NULL, p_fund, True) a - LEFT JOIN + SELECT b.citco_security_id::text, a.identifier::text AS security_id, a.factor AS serenitas_factor, + a.notional AS serenitas_notional, b.factor AS admin_factor, + b."quantity(end)" AS admin_notional + FROM risk_positions(p_date, NULL, p_fund, True) a + LEFT JOIN ( - SELECT cusip, factor, "quantity(end)", cr.citco_security_id - FROM citco_reports cr + SELECT cusip, factor, "quantity(end)", cr.citco_security_id + FROM citco_reports cr WHERE fund=p_fund AND cusip IS NOT NULL AND period_end_date=p_date - ) b + ) b ON a.identifier=b.cusip; END; $$ LANGUAGE PLPGSQL; @@ -5172,9 +5172,9 @@ AS SELECT tc.date AS settle_date, tc.accrued::numeric(11,2) AS accrued, (COALESCE(tc.principal, 0::double precision) + COALESCE(tc.accrued, 0::double precision))::numeric(11,2) AS total_cashflow, tc.currency, - cds.fund, - cds.cpty_id + cds.fund, + cds.cpty_id FROM tranche_cashflows tc LEFT JOIN cds ON tc.tranche_id = cds.id LEFT JOIN counterparties ON cds.cp_code::text = counterparties.code::text - ORDER BY tc.date DESC, cds.fund, cds.cp_code;
\ No newline at end of file + ORDER BY tc.date DESC, cds.fund, cds.cp_code; |