diff options
| -rw-r--r-- | sql/dawn.sql | 35 |
1 files changed, 35 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 64fcf7d7..97f8d5e2 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -563,6 +563,41 @@ END IF; 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, + 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, + clean_nav float, accrued float) AS $$ +DECLARE + days integer; + eur_fx float; + params text; + sqlquery text; +BEGIN +days:=days_accrued(p_date); +SELECT eurusd INTO eur_fx FROM fx WHERE date=p_date; + +sqlquery := 'WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.tenor, c.indexfactor/100. AS fact, + cds_globeop_name(a.security_id, a.fixed_rate, a.maturity, c.index, c.tenor) + FROM list_cds_positions_by_strat($1) a + LEFT JOIN index_desc c ON (a.security_id=c.redindexcode AND a.maturity=c.maturity)), +index_price AS (SELECT index, series, version, d.tenor, closeprice, d.duration, d.closespread, d.theta + FROM index_quotes d WHERE date=$1) +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 * + (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END), + 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)'; +RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, days; +END; +$$ LANGUAGE plpgsql; + CREATE OR REPLACE function list_tranche_marks(p_date date) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_type, p_series smallint, p_version smallint, p_tenor tenor, maturity date, |