diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 61 |
1 files changed, 12 insertions, 49 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index c4b6ad94..07d34ced 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -969,59 +969,22 @@ 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, - p_version smallint, tenor tenor, maturity date, notional float, +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 $$ -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, $4'; - and_clause := 'AND strat = ltrim($4::text, ''SER_'')::strategy'; -ELSE - params := '$1'; - 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), -globeop_marks AS (SELECT security_id, maturity, sum(endqty) AS current_notional, - sum(endbooknav) AS nav - FROM (SELECT *, split_part(invid, ''_'', 2) AS security_id, - split_part(invid, ''_'', 6)::date AS maturity - FROM valuation_reports - WHERE periodenddate=$1 and invid like ''CDS\_%%'' %s) a - GROUP BY security_id, maturity) -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 $2 ELSE 1 END), - -temp.notional * temp.fixed_rate/100. * temp.fact * $3 / 360 * - (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END), - globeop_marks.nav, globeop_marks.current_notional -FROM temp -LEFT JOIN index_price USING (index, series, version, tenor) -LEFT JOIN globeop_marks USING (security_id, maturity)', params, and_clause); -IF strat IS NOT NULL THEN - RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, days, strat; -ELSE - RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, days; -END IF; +BEGIN +RETURN QUERY +SELECT l.*, b.globeop_nav, b.globeop_notional from list_cds_marks_pre(p_date, strat, 'SERCGMAST') 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\_%') a + GROUP BY date, a.security_id, a.maturity HAVING date=p_date) b using (security_id, maturity); END; $$ LANGUAGE plpgsql; |