diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 22 |
1 files changed, 12 insertions, 10 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index a33fc96a..22d96ffb 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -237,8 +237,8 @@ DECLARE query text; BEGIN - query := 'WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, %s -SUM(cds.notional * (CASE WHEN cds.protection=''Buyer'' THEN -1 ELSE 1 END)) + query := 'WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, +cds.fixed_rate, %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.trade_date <=$1 %s) SELECT DISTINCT ON (tmp.security_id, tmp.maturity %s) * FROM tmp WHERE tmp.notional!=0'; @@ -256,22 +256,24 @@ END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds_positions (p_date date) -RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, notional float) AS $$ +RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, + fixed_rate float, notional float) AS $$ BEGIN RETURN QUERY EXECUTE query_positions('cds') USING p_date; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_tranche_positions(p_date date) -RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, attach smallint, - detach smallint, notional float) AS $$ +RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, + fixed_rate float, attach smallint, detach smallint, notional float) AS $$ BEGIN RETURN QUERY EXECUTE query_positions('tranche') USING p_date; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_abscds_positions(p_date date) -RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, notional float) AS $$ +RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, + fixed_rate float, notional float) AS $$ BEGIN RETURN QUERY EXECUTE query_positions('abs') USING p_date; END; @@ -279,21 +281,21 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds_marks(p_date date) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, - notional float, factor float, coupon integer, duration float, clean_nav float, accrued float) AS $$ + notional float, factor float, coupon float, duration float, clean_nav float, accrued float) AS $$ DECLARE days integer; BEGIN days:=days_accrued(p_date); RETURN QUERY -WITH temp AS (SELECT a.*, c.index, c.series, c.version, d,tenor, c.indexfactor/100. AS fact, d.coupon +WITH temp AS (SELECT a.*, c.index, c.series, c.version, d.tenor, c.indexfactor/100. AS fact FROM list_cds_positions(p_date) a LEFT JOIN index_version c ON a.security_id=c.redindexcode LEFT JOIN index_desc d USING (redindexcode, maturity)), index_price AS (SELECT index, series, version, tenor, closeprice, index_quotes.duration FROM index_quotes WHERE date=p_date) -SELECT temp.security_id, temp.security_desc, temp.maturity, temp.notional, temp.fact, temp.coupon, +SELECT temp.security_id, temp.security_desc, temp.maturity, temp.notional, temp.fact, temp.fixed_rate/100, index_price.duration, (index_price.closeprice/100.-1)*temp.notional*temp.fact, -temp.notional*temp.coupon/10000.*temp.fact*days/360 +temp.notional*temp.fixed_rate/100.*temp.fact*days/360 FROM temp LEFT JOIN index_price ON temp.index::text=index_price.index AND temp.series=index_price.series |