diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 18 |
1 files changed, 9 insertions, 9 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index db07bec6..85789749 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -512,7 +512,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 * (CASE WHEN cds.protection=''Buyer'' THEN 1 ELSE -1 END)) OVER (PARTITION BY cds.security_id, cds.maturity, cds.attach, cds.detach) AS notional FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date> $1) and cds.trade_date <=$1 %s) SELECT DISTINCT ON (tmp.security_id, tmp.maturity %s) * FROM tmp WHERE tmp.notional!=0'; @@ -553,7 +553,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 * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END)) OVER (PARTITION BY cds.security_id, cds.maturity, 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.attach is NULL AND cds.folder != 'MBSCDS') @@ -628,9 +628,9 @@ SELECT temp.security_id, temp.security_desc, temp.index, temp.series, temp.version, temp.tenor, temp.maturity, temp.notional, temp.fact, temp.cds_globeop_name, temp.fixed_rate/100, index_price.duration, index_price.theta, index_price.closeprice, index_price.closespread, - (index_price.closeprice/100.-1) * temp.notional * temp.fact * + (1.-index_price.closeprice/100.) * temp.notional * temp.fact * (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END), - temp.notional * temp.fixed_rate/100. * temp.fact * $3 / 360 * + -temp.notional * temp.fixed_rate/100. * temp.fact * $3 / 360 * (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END) FROM temp LEFT JOIN index_price USING (index, series, version, tenor)', params); @@ -643,7 +643,7 @@ END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds_marks_by_strat(p_date date) -RETURNS TABLE(security_id varchar(12), security_desc varchar(32), strategy csd_strat, +RETURNS TABLE(security_id varchar(12), security_desc varchar(32), strategy cds_strat, p_index index_type, p_series smallint, p_version smallint, tenor tenor, maturity date, notional float, factor float, name text, coupon float, duration float, theta float, price float, closespread float, @@ -667,9 +667,9 @@ SELECT temp.security_id, temp.security_desc, temp.folder, temp.index, temp.series, temp.version, temp.tenor, temp.maturity, temp.notional, temp.fact, temp.cds_globeop_name, temp.fixed_rate/100, index_price.duration, index_price.theta, index_price.closeprice, index_price.closespread, - (index_price.closeprice/100.-1) * temp.notional * temp.fact * + (1.-index_price.closeprice/100.) * temp.notional * temp.fact * (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END), - temp.notional * temp.fixed_rate/100. * temp.fact * $3 / 360 * + -temp.notional * temp.fixed_rate/100. * temp.fact * $3 / 360 * (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END) FROM temp LEFT JOIN index_price USING (index, series, version, tenor)'; @@ -706,9 +706,9 @@ risk_num AS (SELECT DISTINCT ON (series, attach, detach, tenor) * from risk_num_ SELECT temp.security_id, temp.security_desc, temp.index, temp.series, temp.version, temp.tenor, temp.maturity, temp.notional, temp.fact, tranche_spread::integer, - temp.notional * temp.fact * (-upfront_mid) * + temp.notional * temp.fact * upfront_mid * (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END), - temp.notional * temp.fact * tranche_spread/10000. * days / 360 * + -temp.notional * temp.fact * tranche_spread/10000. * days / 360 * (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END), temp.initial_margin_percentage, risk_num.theta, risk_num.duration, risk_num.tranchedelta, risk_num.trancheupfrontmid, risk_num.indexrefprice, risk_num.indexrefspread, |