diff options
| -rw-r--r-- | sql/dawn.sql | 24 |
1 files changed, 10 insertions, 14 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index a8f59409..661b6126 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1637,22 +1637,16 @@ WINDOW w AS (PARTITION BY maturity_date) ) AS ss WHERE row_number=1; -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, - factor float, coupon float, duration float, theta float, price float, - closespread float, clean_nav float, accrued float, - globeop_nav float, globeop_notional float) AS $$ +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 character varying, security_desc character varying, index index_type, series smallint, version smallint, tenor tenor, maturity date, notional double precision, factor double precision, coupon double precision, duration double precision, theta double precision, price double precision, closespread double precision, clean_nav double precision, accrued double precision, globeop_nav double precision, globeop_notional double precision) + LANGUAGE plpgsql +AS $function$ DECLARE sqlquery text; and_clause text; BEGIN IF strat IS NOT NULL THEN - IF p_fund = 'ISOSEL' THEN + IF p_fund = 'ISOSEL' OR p_fund = 'CRSE' THEN and_clause := 'AND split_part(strategy, ''/'', 2) = $2::text'; ELSE and_clause := 'AND strat = ltrim($2::text, ''SER_'')::strategy'; @@ -1678,7 +1672,7 @@ USING (security_id, maturity)', and_clause); -- 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'; ELSIF p_fund = 'BRINKER' THEN sqlquery := 'SELECT *, 0::double precision, 0::double precision FROM list_cds_marks_pre($1, $2, $3)'; -ELSIF p_fund = 'ISOSEL' THEN +ELSIF p_fund = 'ISOSEL' OR p_fund = 'CRSE' THEN sqlquery := format(' SELECT l.*, b.dirty_nav, b.position FROM list_cds_marks_pre($1, $2, $3) l LEFT JOIN index_version ON security_id=redindexcode @@ -1686,11 +1680,13 @@ LEFT JOIN index_maturity USING (INDEX, series, maturity) LEFT JOIN ( SELECT ticker_bbg AS bbg_id, -sum(position*end_princ_factor) AS position, sum(end_mkt_value+total_acc_int) AS dirty_nav FROM isosel_accrued WHERE instr_type=''Credit Default Swap'' -AND ticker_bbg IS NOT NULL AND status in (''Open'', ''P'') %s GROUP BY period_end_date, ticker_bbg HAVING period_end_date=$1 ) b USING (bbg_id)', and_clause); +AND ticker_bbg IS NOT NULL AND status in (''Open'', ''P'') %s GROUP BY period_end_date, fund, ticker_bbg HAVING period_end_date=$1 AND fund=$3) b USING (bbg_id)', and_clause); END IF; RETURN QUERY EXECUTE sqlquery USING p_date, strat, p_fund; END; -$$ LANGUAGE plpgsql; +$function$ +; + |