diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 60 |
1 files changed, 59 insertions, 1 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index c19c77fb..a40c22bb 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -766,7 +766,7 @@ RETURN QUERY EXECUTE query_positions('abs') USING p_date, fund; END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE function list_cds_marks(p_date date, strat cds_strat DEFAULT NULL::cds_strat) +CREATE OR REPLACE function list_cds_marks_old(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, factor float, name text, coupon float, duration float, theta float, price float, closespread float, @@ -810,6 +810,64 @@ 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, + 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, + 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 eurusd INTO eur_fx FROM fx WHERE date=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 index, series, version, d.tenor, closeprice, d.duration2, d.closespread, d.theta2 + FROM index_quotes d WHERE date=$1), +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; +END; +$$ LANGUAGE plpgsql; + + CREATE OR REPLACE function list_cds_marks_by_strat(p_date date) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), strategy cds_strat, p_index index_type, p_series smallint, |