diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 17 |
1 files changed, 10 insertions, 7 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index ad552352..e55ee7bb 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -647,7 +647,7 @@ DECLARE BEGIN query := 'WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, -cds.fixed_rate, cds.currency, %s SUM(cds.notional * (CASE WHEN cds.protection=''Buyer'' THEN 1 ELSE -1 END)) +cds.fixed_rate, cds.currency, %s SUM(cds.notional) OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach) AS notional FROM list_cds($1, $2) cds WHERE %s) SELECT DISTINCT ON (tmp.security_id, tmp.maturity %s) * FROM tmp WHERE tmp.notional!=0'; @@ -691,7 +691,9 @@ RETURNS SETOF LIST_CDS AS $$ BEGIN RETURN QUERY SELECT id, trade_date, security_id, security_desc, maturity, fixed_rate, currency, - folder, protection, notional - coalesce(terminated_amount, 0.) AS notional, + folder, protection, (notional - coalesce(terminated_amount, 0.)) * + (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END) + AS notional, orig_attach, orig_detach, attach, detach, account_code, initial_margin_percentage, cpty_id FROM cds LEFT JOIN ( @@ -729,7 +731,7 @@ BEGIN RETURN QUERY WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, cds.currency, cds.folder, - SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END)) + SUM(cds.notional) OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder) AS notional FROM list_cds(p_date, p_fund) cds WHERE (cds.attach is NULL AND cds.folder != 'MBSCDS')) SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.folder) * FROM tmp WHERE tmp.notional!=0; @@ -744,7 +746,7 @@ BEGIN RETURN QUERY WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, cds.currency, cds.folder, - SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END)) + SUM(cds.notional) OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder, cds.account_code) AS notional FROM list_cds(p_date) cds WHERE (cds.attach is NULL AND cds.folder != 'MBSCDS' AND cds.fcm=fcm)) SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.folder) * FROM tmp WHERE tmp.notional!=0; @@ -798,7 +800,7 @@ RETURN QUERY WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, cds.currency, cds.orig_attach, cds.orig_detach, cds.attach, cds.detach, cds.folder, - SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END)) + SUM(cds.notional) OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach, cds.folder) AS notional 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; @@ -955,6 +957,7 @@ $$ LANGUAGE plpgsql; CREATE TABLE tranche_risk( date date, tranche_id integer REFERENCES cds(id), + notional float, clean_nav float, accrued float, duration float, @@ -1732,8 +1735,8 @@ $$ LANGUAGE plpython3u; CREATE OR REPLACE VIEW globeop_tranche_risk AS SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, maturity, orig_attach, - orig_detach, CASE protection WHEN 'Buyer' THEN notional ELSE -notional END AS notional, - endqty as globeop_notional, + orig_detach, tranche_risk.notional, + globeop_notional, clean_nav * coalesce(fx, 1.) as serenitas_clean_nav, globeop_clean_nav, accrued * coalesce(fx, 1.) as serenitas_accrued, globeop_accrued, |