diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 51 |
1 files changed, 40 insertions, 11 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index d72be9b3..f39e01fd 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -644,24 +644,56 @@ 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)) OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach) AS notional -FROM cds WHERE cds.fund=$2 AND (cds.termination_date is NULL OR cds.termination_date[1]> $1) - AND cds.trade_date <=$1 %s) +FROM list_cds($1, $2) cds WHERE %s) SELECT DISTINCT ON (tmp.security_id, tmp.maturity %s) * FROM tmp WHERE tmp.notional!=0'; IF p_type = 'tranche' THEN RETURN format(query, 'cds.orig_attach,cds.orig_detach,cds.attach,cds.detach,' 'cds.initial_margin_percentage,', - 'AND cds.orig_attach is NOT NULL', ',tmp.orig_attach'); + 'cds.orig_attach is NOT NULL', ',tmp.orig_attach'); ELSIF p_type = 'cds' THEN - RETURN format(query, '', 'AND 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, '', 'AND cds.folder=''MBSCDS''', ''); + RETURN format(query, '', 'cds.folder=''MBSCDS''', ''); ELSE RETURN format(query, 'cds.attach, cds.detach,', '', ',tmp.attach'); END IF; END; $$ LANGUAGE plpgsql; +CREATE TYPE LIST_CDS AS( + trade_date date, + security_id varchar(12), + security_desc varchar(32), + maturity date, + fixed_rate float, + currency currency, + folder cds_strat, + protection protection, + notional float, + orig_attach smallint, + orig_detach smallint, + attach float, + detach float, + fcm text +); + +CREATE OR REPLACE function list_cds(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) +RETURNS SETOF LIST_CDS AS $$ +BEGIN +RETURN QUERY +SELECT trade_date, security_id, security_desc, maturity, fixed_rate, currency, + folder, protection, notional - coalesce(terminated_amount, 0.) AS notional, + orig_attach, orig_detach, attach, detach, account_code +FROM cds LEFT JOIN ( + SELECT id, SUM(termination_amount ) AS terminated_amount + FROM (SELECT id, unnest(termination_amount) as termination_amount, + unnest(termination_date) as termination_date FROM cds) a + WHERE termination_date <= p_date GROUP BY id) b USING (id) + WHERE fund=p_fund and notional != 0. and trade_date <= p_date; +END; +$$ LANGUAGE plpgsql; + CREATE OR REPLACE function list_cds_positions (p_date date, strat cds_strat DEFAULT NULL::cds_strat, fund fund DEFAULT 'SERCGMAST'::fund) @@ -690,14 +722,13 @@ RETURN QUERY cds.currency, cds.folder, 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[1] > p_date) AND - cds.trade_date <= p_date AND cds.attach is NULL AND cds.folder != 'MBSCDS' AND cds.fund=p_fund) + FROM list_cds(p_date, p_fund) cds WHERE (cds.attach is NULL AND cds.folder != 'MBSCDS')) SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.folder) * FROM tmp WHERE tmp.notional!=0; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds_positions_by_strat_fcm(p_date date, - fcm text) + p_fcm text) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, fixed_rate float, currency currency, folder cds_strat, notional float) AS $$ BEGIN @@ -706,9 +737,7 @@ RETURN QUERY cds.currency, cds.folder, SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END)) OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder, cds.account_code) AS notional - FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date[1] > p_date) AND - cds.trade_date <= p_date AND cds.attach is NULL AND cds.folder != 'MBSCDS' - AND cds.account_code=fcm) + FROM list_cds(p_date) cds WHERE (cds.attach is NULL AND cds.folder != 'MBSCDS' AND cds.fcm=fcm)) SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.folder) * FROM tmp WHERE tmp.notional!=0; END; $$ LANGUAGE plpgsql; |