diff options
| -rw-r--r-- | sql/dawn.sql | 40 |
1 files changed, 31 insertions, 9 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index c0db19e3..0b1dfc92 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1067,7 +1067,21 @@ END IF; END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE function list_cds_marks(p_date date, strat cds_strat DEFAULT NULL::cds_strat, p_fund fund DEFAULT 'SERCGMAST'::fund) +CREATE OR REPLACE VIEW bowdst_index AS +SELECT + as_of_date, + substring(security_description_1 FROM '(IG|HY|EU|XO)')::index_type AS p_index, + substring(security_description_1 FROM '(?:IG|HY|EUR|XOVER)\.([0-9]{2})')::smallint as p_series, + maturity_date AS maturity, + (CASE WHEN coupon_rate=0 THEN current_notional ELSE -current_notional END) AS admin_notional, + base_market_value AS admin_clean_nav +FROM bowdst_val +WHERE security_description_1 LIKE 'CCP%' AND base_market_value != 0.; + +CREATE OR REPLACE function list_cds_marks( + p_date date, + strat cds_strat DEFAULT NULL::cds_strat, + p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), index index_type, series smallint, version smallint, tenor tenor, maturity date, notional float, @@ -1083,15 +1097,24 @@ IF strat IS NOT NULL THEN ELSE and_clause := ''; END IF; -sqlquery := format('SELECT l.*, b.globeop_nav, b.globeop_notional from list_cds_marks_pre($1, $2, $3) l +IF p_fund = 'SERCGMAST' THEN +sqlquery := format(' +SELECT l.*, b.globeop_nav, b.globeop_notional FROM list_cds_marks_pre($1, $2, $3) l JOIN (SELECT a.security_id, a.maturity, sum(endbooknav) AS globeop_nav, - sum(endqty) AS globeop_notional - FROM (SELECT periodenddate as date, endqty, endbooknav, split_part(invid, ''_'', 2) AS security_id, - split_part(invid, ''_'', 6)::date AS maturity - FROM valuation_reports where invid like ''CDS\_%%'' %s) a - GROUP BY date, a.security_id, a.maturity HAVING date=$1) b using (security_id, maturity)', and_clause); + sum(endqty) AS globeop_notional + FROM (SELECT periodenddate as date, + endqty, + endbooknav, + split_part(invid, ''_'', 2) AS security_id, + split_part(invid, ''_'', 6)::date AS maturity + FROM valuation_reports WHERE invid LIKE ''CDS\_%%'' %s) a + GROUP BY date, a.security_id, a.maturity HAVING date=$1) b +USING (security_id, maturity)', and_clause); +ELSIF p_fund = 'BOWDST' THEN +sqlquery := 'SELECT a.*, bowdst_index.admin_clean_nav, bowdst_index.admin_notional FROM list_cds_marks_pre($1, $2, $3) a LEFT JOIN bowdst_index USING (p_index, p_series, maturity) WHERE as_of_date=$1'; +END IF; RETURN QUERY EXECUTE sqlquery USING p_date, strat, p_fund; -end; +END; $$ LANGUAGE plpgsql; @@ -2199,4 +2222,3 @@ CREATE TABLE tranche_cashflows( currency currency, PRIMARY KEY (date, tranche_id) ); - |