diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 31 |
1 files changed, 13 insertions, 18 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index e8f83a4f..2d920a12 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1543,15 +1543,13 @@ RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_ name text, 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; 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'; + params := '$1, $3'; ELSE params := '$1'; END IF; @@ -1569,14 +1567,14 @@ SELECT 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 * + -temp.notional * temp.fact * cds_accrued($1, temp.fixed_rate/100., TRUE, temp.currency) (CASE WHEN temp.currency = ''EUR'' THEN $2 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, eur_fx, days, strat; + RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, strat; ELSE - RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, days; + RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx; END IF; END; $$ LANGUAGE plpgsql; @@ -1588,17 +1586,15 @@ RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_ 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'; + params := '$1, $4, $2'; + and_clause := 'AND strat = ltrim($4::text, ''SER_'')::strategy'; ELSE params := '$1, NULL, $2'; and_clause := ''; @@ -1616,14 +1612,14 @@ SELECT 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 * + -temp.notional * temp.fact * cds_accrued($1, temp.fixed_rate/100, FALSE, temp.currency::text) * (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; + RETURN QUERY EXECUTE sqlquery USING p_date, fund, eur_fx, strat; ELSE - RETURN QUERY EXECUTE sqlquery USING p_date, fund, eur_fx, days; + RETURN QUERY EXECUTE sqlquery USING p_date, fund, eur_fx; END IF; END; $$ LANGUAGE plpgsql; @@ -1943,11 +1939,10 @@ from dates import imm_date return imm_date(p_date) $$ LANGUAGE plpython3u; -CREATE OR REPLACE function days_accrued(p_date date) -RETURNS integer AS $$ -from dates import days_accrued -return days_accrued(p_date) -$$ LANGUAGE plpython3u; +CREATE OR REPLACE FUNCTION cds_accrued(date, double precision, bool, text DEFAULT 'USD') RETURNS integer +AS '$libdir/serenitas_date', 'cds_accrued' +LANGUAGE C STRICT; + CREATE MATERIALIZED VIEW factors_history AS WITH temp AS ( |