diff options
| -rw-r--r-- | sql/dawn.sql | 20 |
1 files changed, 11 insertions, 9 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 82c2d86c..aaef5679 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -790,12 +790,12 @@ sqlquery := format( cds_globeop_name(a.security_id, a.fixed_rate, a.maturity, c.index, c.tenor) 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.duration, d.closespread, d.theta +index_price AS (SELECT index, series, version, d.tenor, closeprice, d.duration, d.closespread, d.theta2 FROM index_quotes d WHERE date=$1) SELECT temp.security_id, temp.security_desc, temp.index, temp.series, temp.version, temp.tenor, temp.maturity, temp.notional, temp.fact, temp.cds_globeop_name, temp.fixed_rate/100, - index_price.duration, index_price.theta, index_price.closeprice, index_price.closespread, + index_price.duration, 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 * @@ -919,7 +919,7 @@ CREATE TABLE tranche_risk( index_duration float, PRIMARY KEY (date, tranche_id)) -CREATE OR REPLACE function list_tranche_marks(p_date date) +CREATE OR REPLACE function list_tranche_marks(p_date date, 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, p_tenor tenor, maturity date, notional float, factor float, coupon integer, clean_nav float, @@ -936,7 +936,7 @@ SELECT eurusd INTO eur_fx FROM fx WHERE date=p_date; RETURN QUERY WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.basketid, d.tenor, (a.detach-a.attach)/(a.orig_detach-a.orig_attach) * c.indexfactor / 100 AS fact - FROM list_tranche_positions(p_date) a + FROM list_tranche_positions(p_date, fund) a LEFT JOIN index_version c ON a.security_id=c.redindexcode LEFT JOIN index_maturity d USING (index, series, maturity)), risk_num AS (SELECT DISTINCT ON (index, series, a.attach, a.detach, tenor) * from risk_num_per_quote a @@ -1160,11 +1160,13 @@ CREATE OR REPLACE function latest_sim(p_date date) RETURNS integer AS $$ DECLARE id integer; BEGIN -SELECT model_id_sub into id FROM model_versions a - INNER JOIN model_versions_nonagency USING (model_id_sub) - INNER JOIN simulations_nonagency USING (simulation_id) - WHERE (date(start_time) <= p_date) AND (description='normal') - ORDER BY a.start_time DESC limit 1; +SELECT model_id_sub into id + FROM priced + INNER JOIN model_versions USING (model_id_sub) + INNER JOIN model_versions_nonagency USING (model_id_sub) + INNER JOIN simulations_nonagency USING (simulation_id) + WHERE description = 'normal' AND timestamp >= p_date + LIMIT 1; RETURN id; End; $$ LANGUAGE plpgsql; |