diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 37 |
1 files changed, 28 insertions, 9 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index f39e01fd..ff6e7de8 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -662,6 +662,7 @@ END; $$ LANGUAGE plpgsql; CREATE TYPE LIST_CDS AS( + id integer, trade_date date, security_id varchar(12), security_desc varchar(32), @@ -675,22 +676,24 @@ CREATE TYPE LIST_CDS AS( orig_detach smallint, attach float, detach float, - fcm text + fcm text, + initial_margin_percentage float ); CREATE OR REPLACE function list_cds(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS SETOF LIST_CDS AS $$ BEGIN RETURN QUERY -SELECT trade_date, security_id, security_desc, maturity, fixed_rate, currency, +SELECT id, trade_date, security_id, security_desc, maturity, fixed_rate, currency, folder, protection, notional - coalesce(terminated_amount, 0.) AS notional, - orig_attach, orig_detach, attach, detach, account_code + orig_attach, orig_detach, attach, detach, account_code, initial_margin_percentage FROM cds LEFT JOIN ( SELECT id, SUM(termination_amount ) AS terminated_amount FROM (SELECT id, unnest(termination_amount) as termination_amount, unnest(termination_date) as termination_date FROM cds) a WHERE termination_date <= p_date GROUP BY id) b USING (id) - WHERE fund=p_fund and notional != 0. and trade_date <= p_date; +WHERE fund=p_fund and notional IS DISTINCT FROM terminated_amount + AND trade_date <= p_date AND maturity > p_date; END; $$ LANGUAGE plpgsql; @@ -791,8 +794,7 @@ RETURN QUERY cds.attach, cds.detach, cds.folder, SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END)) OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach, cds.folder) AS notional - FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date > p_date) AND - cds.trade_date <= p_date AND cds.orig_attach is NOT NULL AND cds.fund=p_fund) + FROM list_cds(p_date, p_fund) cds WHERE cds.orig_attach is NOT NULL) SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.orig_attach, tmp.folder) * FROM tmp WHERE tmp.notional!=0; END; $$ LANGUAGE plpgsql; @@ -1774,8 +1776,8 @@ FROM swaptions JOIN counterparties ON cp_code = code CREATE OR REPLACE VIEW cds_trades AS SELECT id, dealid, - termination_date as trade_date, - notional, + termination_date AS trade_date, + termination_amount AS notional, security_desc, security_id, CASE WHEN termination_cp=cp_code THEN @@ -1798,7 +1800,24 @@ SELECT id, END AS protection, -termination_fee AS upfront, ref -FROM cds JOIN counterparties ON termination_cp = code WHERE termination_date IS NOT NULL +FROM (select id, + dealid, + unnest(termination_date) AS termination_date, + unnest(termination_amount) AS termination_amount, + security_desc, + security_id, + unnest(termination_cp) AS termination_cp, + folder, + fund, + orig_attach, + orig_detach, + attach, + detach, + protection, + unnest(termination_fee) AS termination_fee, + cp_code, + ref from cds where termination_date is not null) a +left JOIN counterparties on termination_cp=code UNION ALL ( SELECT id, dealid, |