diff options
| -rw-r--r-- | sql/dawn.sql | 44 |
1 files changed, 22 insertions, 22 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index dbe531db..16f6b489 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -231,6 +231,28 @@ BEGIN END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE function risk_positions(p_date date, p_assetclass asset_class) RETURNS TABLE +(description varchar(32), identifier varchar(12), notional float, price float, +strategy bond_strat, factor float, local_market_value float, usd_market_value float, +curr_cpn float, int_acc float, last_pay_date date, principal_payment float, +accrued_payment float, last_settle_date date) AS $$ +BEGIN + RETURN QUERY + SELECT a.description, a.identifier, a.notional, c.price, a.strategy, coalesce(b.factor, 1), + c.price/100. * a.notional * (CASE WHEN coalesce(b.factor,1)=0 THEN 1 ELSE coalesce(b.factor,1) END), + c.price/100. * a.notional * (CASE WHEN coalesce(b.factor,1)=0 THEN 1 ELSE coalesce(b.factor,1) END) * fxrate, + b.coupon, + a.notional * coalesce(b.factor,1) * fxrate * + yearfrac(case WHEN start_accrued_date>=p_date+1 THEN b.prev_cpn_date ELSE start_accrued_date END, p_date+1, daycount) * b.coupon/100., + b.last_pay_date, a.principal_payment, a.accrued_payment, a.last_settle_date + FROM list_positions(p_date, p_assetclass) a + LEFT JOIN factors_history(p_date) b USING (identifier) + LEFT JOIN list_marks(p_date, True) c USING (identifier) + LEFT JOIN fx_rate(p_date) USING (currency) + ORDER by identifier asc; +END; +$$ LANGUAGE plpgsql; + CREATE OR REPLACE VIEW orig_cds AS SELECT DISTINCT ON (dealid) dealid, folder, index, series, version, tenor, fixed_rate, notional, upfront, protection, attach, detach, trade_date, upfront_settle_date FROM cds @@ -470,28 +492,6 @@ BEGIN END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE function risk_positions(p_date date, p_assetclass asset_class) RETURNS TABLE -(description varchar(32), identifier varchar(12), notional float, price float, -strategy bond_strat, factor float, local_market_value float, usd_market_value float, -curr_cpn float, int_acc float, last_pay_date date, principal_payment float, -accrued_payment float, last_settle_date date) AS $$ -BEGIN - RETURN QUERY - SELECT a.description, a.identifier, a.notional, c.price, a.strategy, coalesce(b.factor, 1), - c.price/100. * a.notional * (CASE WHEN coalesce(b.factor,1)=0 THEN 1 ELSE coalesce(b.factor,1) END), - c.price/100. * a.notional * (CASE WHEN coalesce(b.factor,1)=0 THEN 1 ELSE coalesce(b.factor,1) END) * fxrate, - b.coupon, - a.notional * coalesce(b.factor,1) * fxrate * - yearfrac(case WHEN start_accrued_date>=p_date+1 THEN b.prev_cpn_date ELSE start_accrued_date END, p_date+1, daycount) * b.coupon/100., - b.last_pay_date, a.principal_payment, a.accrued_payment, a.last_settle_date - FROM list_positions(p_date, p_assetclass) a - LEFT JOIN factors_history(p_date) b USING (identifier) - LEFT JOIN list_marks(p_date, True) c USING (identifier) - LEFT JOIN fx_rate(p_date) USING (currency) - ORDER by identifier asc; -END; -$$ LANGUAGE plpgsql; - -- Not sure how to map enums so use text for now CREATE FOREIGN TABLE priced( |