diff options
| -rw-r--r-- | sql/dawn.sql | 18 |
1 files changed, 18 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 22d96ffb..b3fb3a15 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -327,6 +327,24 @@ LEFT JOIN tranche_price USING (basketid, attach, detach, tenor); END $$ LANGUAGE plpgsql; +CREATE OR REPLACE function list_abscds_marks(p_date date) +RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, + notional float, factor float, fixed_rate float, clean_nav float, accrued float) AS $$ +BEGIN +RETURN QUERY +WITH temp AS (SELECT a.*, b.price, c.factor, d.start_accrued_date FROM list_abscds_positions(p_date) a +LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price + FROM marks WHERE date<=p_date ORDER BY identifier, date desc) b +ON a.security_id=b.identifier +LEFT JOIN factors_history(p_date) c ON a.security_id=c.identifier +LEFT JOIN securities d ON a.security_id=d.identifier) +SELECT temp.security_id, temp.security_desc, temp.maturity, temp.notional, temp.factor, temp.fixed_rate, +temp.notional*temp.factor*(temp.price-100)/100, +yearfrac(temp.start_accrued_date, p_date+1, 'ACT/360')*temp.fixed_rate/100*temp.notional*temp.factor +FROM temp +END +$$ LANGUAGE plpgsql; + CREATE OR REPLACE function days_accrued(p_date date) RETURNS integer AS $$ from dates import days_accrued |