diff options
Diffstat (limited to 'sql/et_tables.sql')
| -rw-r--r-- | sql/et_tables.sql | 34 |
1 files changed, 34 insertions, 0 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 6f307c06..da6cd0d6 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -597,6 +597,39 @@ CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[]) ALTER FUNCTION et_cusip_details(varchar(10)[]) OWNER TO et_user; +CREATE OR REPLACE FUNCTION historical_cusip_details(p_date date, p_cusips VARIADIC varchar(10)[]) + RETURNS TABLE(orig_moody text, curr_moody text, issuer text, + manager text, spread float, coupon float, issuedate date, + reinvenddate date, maturity date, stale_percentage float, + cdo_percentage float, wap_basis float, portfolio_spread_5y float, + portfolio_spread float, subordination float, thickness float, + mvoc float, mvcoverage float, pricingdate date, delta float, + duration float, wal float, price float) AS $$ + BEGIN + RETURN QUERY SELECT cusip_ref.orig_moody, a.curr_moody, "Deal Name", deal_indicative.manager, + cusip_ref.spread/100, a.coupon/100, deal_issue_date, reinv_end_date, deal_indicative.maturity, + stalepercentage, cdopercentage, wapbasis, dealspread5y, dealspread, curr_attach/100, + (curr_detach-curr_attach)/100, b.mvoc, b.mvcoverage, b.updatedate, + b.delta, b.duration, b.wal, b.price + FROM cusip_ref + LEFT JOIN + (SELECT DISTINCT ON (cusip) * FROM cusip_update WHERE updatedate<=p_date ORDER by cusip, updatedate DESC) a + USING (cusip) + LEFT JOIN + (SELECT DISTINCT ON (cusip) * FROM et_cusip_model_numbers WHERE updatedate<=p_date + ORDER by cusip, updatedate DESC) b + USING (cusip) + LEFT JOIN (SELECT DISTINCT ON (dealname) * FROM et_deal_model_numbers WHERE updatedate<=p_date + ORDER by dealname, updatedate DESC) c + USING (dealname) + LEFT JOIN deal_indicative USING (dealname) + RIGHT JOIN (SELECT unnest(p_cusips) AS cusip, generate_subscripts(p_cusips, 1) AS id) d USING (cusip) ORDER BY d.id; + END; + $$ LANGUAGE plpgsql; + +ALTER FUNCTION historical_cusip_details(varchar(10)[]) + OWNER TO et_user; + CREATE OR REPLACE FUNCTION historical_cusip_risk(p_date date, p_cusips VARIADIC varchar(10)[]) RETURNS TABLE(duration float, delta float, price float, pricingdate date) AS $$ BEGIN @@ -612,6 +645,7 @@ CREATE OR REPLACE FUNCTION historical_cusip_risk(p_date date, p_cusips VARIADIC ALTER FUNCTION historical_cusip_risk(date, varchar(10)[]) OWNER TO et_user; + CREATE OR REPLACE FUNCTION dealname_from_cusip(p_cusips VARIADIC varchar(10)[]) -- NOT USED anymore, wasn't working very well RETURNS TABLE(p_dealname varchar(10)) AS $$ |