diff options
| -rw-r--r-- | sql/dawn.sql | 47 |
1 files changed, 47 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index cadd077d..23847c51 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -861,6 +861,53 @@ END IF; END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE function list_cds_marks_pre(p_date date, strat cds_strat DEFAULT NULL::cds_strat, fund fund DEFAULT 'SERCGMAST'::fund) +RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_type, + p_series smallint, + p_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) AS $$ +DECLARE + days integer; + eur_fx float; + params text; + sqlquery text; + and_clause text; +BEGIN +days:=days_accrued(p_date); +SELECT DISTINCT ON (date) eurusd INTO eur_fx FROM fx WHERE date BETWEEN p_date - INTERVAL '3 DAYS' AND p_date; +IF strat IS NOT NULL THEN + params := '$1, $5, $2'; + and_clause := 'AND strat = ltrim($5::text, ''SER_'')::strategy'; +ELSE + params := '$1, NULL, $2'; + and_clause := ''; +END IF; + +sqlquery := format( +'WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.tenor, c.indexfactor/100. AS fact + FROM list_cds_positions(%s) a + LEFT JOIN index_desc c ON (a.security_id=c.redindexcode AND a.maturity=c.maturity)), +index_price AS (SELECT DISTINCT ON (index, series, version, d.tenor) index, series, version, d.tenor, closeprice, d.duration2, d.closespread, d.theta2 + FROM index_quotes d WHERE date BETWEEN $1 - INTERVAL ''3 days'' AND $1 ORDER BY index, series, version, d.tenor, date desc) +SELECT + temp.security_id, temp.security_desc, temp.index, temp.series, temp.version, temp.tenor, + temp.maturity, temp.notional, temp.fact, temp.fixed_rate/100, + index_price.duration2, index_price.theta2, index_price.closeprice, index_price.closespread, + (1.-index_price.closeprice/100.) * temp.notional * temp.fact * + (CASE WHEN temp.currency = ''EUR'' THEN $3 ELSE 1 END), + -temp.notional * temp.fixed_rate/100. * temp.fact * $4 / 360 * + (CASE WHEN temp.currency = ''EUR'' THEN $3 ELSE 1 END) +FROM temp +LEFT JOIN index_price USING (index, series, version, tenor)', params); +IF strat IS NOT NULL THEN + RETURN QUERY EXECUTE sqlquery USING p_date, fund, eur_fx, days, strat; +ELSE + RETURN QUERY EXECUTE sqlquery USING p_date, fund, eur_fx, days; +END IF; +END; +$$ LANGUAGE plpgsql; + CREATE OR REPLACE function list_cds_marks(p_date date, strat cds_strat DEFAULT NULL::cds_strat) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_type, p_series smallint, |