diff options
| -rw-r--r-- | sql/dawn.sql | 15 |
1 files changed, 9 insertions, 6 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index e3412133..2b3ff3e9 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1068,15 +1068,18 @@ END; $$ LANGUAGE plpgsql; 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, +SELECT as_of_date, p_index, p_series, maturity, admin_notional, admin_clean_nav FROM ( +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 abs(base_market_value) > 2.; + sum(base_market_value) OVER w as admin_clean_nav, + row_number() OVER w +FROM bowdst_val WHERE security_description_1 LIKE 'CCP%' +WINDOW w AS (PARTITION BY substring(security_description_1 FROM '(IG|HY|EU|XO)')::index_type, + substring(security_description_1 FROM '(?:IG|HY|EUR|XOVER)(?:\.| )([0-9]{2})')::smallint, + maturity_date) +) AS ss WHERE row_number=1; CREATE OR REPLACE function list_cds_marks( p_date date, |