diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 25 |
1 files changed, 8 insertions, 17 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 861fc4b3..2326969b 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1943,12 +1943,18 @@ LEFT JOIN external_marks_deriv on cpty_id=identifier and external_marks_deriv.da WHERE fund='BRINKER' ORDER BY index, series, orig_attach; -CREATE OR REPLACE FUNCTION tranche_risk_agg(p_date date, p_fund text) +CREATE OR REPLACE FUNCTION tranche_risk_agg(p_date date, p_fund fund) RETURNS TABLE(date date, security_desc varchar(32), index index_type, series smallint, admin_notional float, admin_clean_nav float, maturity date, serenitas_notional float, tranche_factor float, running float, serenitas_clean_nav float, serenitas_accrued float, initial_margin float, theta float, duration float, delta float, upfront float, index_refprice float, index_refspread float, orig_attach smallint, orig_detach smallint, index_duration float, gamma float) AS $$ DECLARE query text; + fund text; BEGIN +IF p_fund = 'SERCGMAST' THEN + fund := 'serenitas'; +ELSE + fund := lower(p_fund::text); +END IF; query := 'SELECT date, security_desc, index, series, sum(admin_notional), sum(admin_clean_nav), maturity, sum(notional), avg(tranche_factor), avg(running), @@ -1964,7 +1970,7 @@ query := 'SELECT date, security_desc, index, series, sum(admin_notional), GROUP BY date, index, security_desc, series, orig_attach, orig_detach, maturity HAVING date=$1 ORDER BY index, series'; -query := format(query, p_fund); +query := format(query, fund); RETURN QUERY EXECUTE query USING p_date; END $$ LANGUAGE plpgsql; @@ -1987,21 +1993,6 @@ LEFT JOIN (SELECT date, 'EUR'::currency AS currency, eurusd AS fx FROM fx) fx US LEFT JOIN external_marks_deriv on cpty_id=identifier and external_marks_deriv.date=tranche_risk.date ORDER BY date desc, index, series, orig_attach; -CREATE OR REPLACE VIEW tranche_risk_agg AS -SELECT date, fund, security_desc, index, series, maturity, sum(notional) AS orig_ntl, - avg(tranche_factor) AS tranche_factor, avg(running) as running, - sum(serenitas_clean_nav) AS serenitas_clean_nav, - sum(serenitas_accrued) AS serenitas_accrued, - sum(cpty_nav) AS cpty_nav, - sum(initial_margin_percentage * abs(notional)/100) as initial_margin, - avg(theta) as theta, avg(duration) as duration, avg(delta) as delta, - avg(upfront) as upfront, avg(index_refprice) as index_refprice, - avg(index_refspread) AS index_refspread, - orig_attach, orig_detach, avg(index_duration) as index_duration, - avg(gamma) AS gamma - FROM tranche_risk_master - GROUP BY date, fund, index, security_desc, series, orig_attach, orig_detach, maturity - ORDER BY index, series; CREATE TABLE fcm_im( date date NOT NULL, |