diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 12 |
1 files changed, 6 insertions, 6 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index b3fb3a15..10211847 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -328,20 +328,20 @@ 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, +RETURNS TABLE(security_id varchar(12), cusip varchar(9), 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 +WITH temp AS (SELECT a.*, b.price, c.factor, d.start_accrued_date, d.cusip 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 +SELECT temp.security_id, temp.cusip, 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; |