diff options
| -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 302e4ead..11890840 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) +cds.fixed_rate, cds.currency, %s %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'; @@ -655,13 +655,16 @@ SELECT DISTINCT ON (tmp.security_id, tmp.maturity %s) * FROM tmp WHERE tmp.notio IF p_type = 'tranche' THEN RETURN format(query, 'cds.orig_attach,cds.orig_detach,cds.attach,cds.detach,' 'cds.initial_margin_percentage,', + 'sum(initial_margin_percentage * abs(notional)/100) ' + 'OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach)' + 'as initial_margin, ', 'cds.orig_attach is NOT NULL', ',tmp.orig_attach'); ELSIF p_type = 'cds' THEN - RETURN format(query, '', 'cds.attach is NULL AND cds.folder!=''MBSCDS''', ''); + RETURN format(query, '', '', 'cds.attach is NULL AND cds.folder!=''MBSCDS''', ''); ELSIF p_type = 'abs' THEN - RETURN format(query, '', 'cds.folder=''MBSCDS''', ''); + RETURN format(query, '', '', 'cds.folder=''MBSCDS''', ''); ELSE - RETURN format(query, 'cds.attach, cds.detach,', '', ',tmp.attach'); + RETURN format(query, 'cds.attach, cds.detach,', '', '', ',tmp.attach'); END IF; END; $$ LANGUAGE plpgsql; @@ -785,7 +788,7 @@ CREATE OR REPLACE function list_tranche_positions(p_date date, fund fund DEFAULT RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, fixed_rate float, currency currency, orig_attach smallint, orig_detach smallint, attach float, detach float, - initial_margin_percentage float, + initial_margin_percentage float, initial_margin float, notional float) AS $$ BEGIN RETURN QUERY EXECUTE query_positions('tranche') USING p_date, fund; @@ -1029,7 +1032,7 @@ CREATE OR REPLACE function list_tranche_marks(p_date date, fund fund DEFAULT 'SE 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, notional float, factor float, coupon integer, clean_nav float, - accrued float, initial_margin_percentage float, theta float, + accrued float, initial_margin float, theta float, duration float, tranchedelta float4, trancheupfrontmid float, indexrefprice float4, indexrefspread smallint, attach smallint, detach smallint, index_duration float) AS $$ @@ -1055,7 +1058,7 @@ SELECT temp.security_id, temp.security_desc, temp.index, temp.series, temp.versi (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END), -temp.notional * temp.fact * trancherunningmid/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, + temp.initial_margin, risk_num.theta, risk_num.duration, risk_num.tranchedelta, risk_num.trancheupfrontmid, risk_num.indexrefprice, risk_num.indexrefspread, temp.orig_attach, temp.orig_detach, risk_num.index_duration FROM temp |