diff options
| -rw-r--r-- | sql/et_tables.sql | 22 |
1 files changed, 17 insertions, 5 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 208d2511..999120ab 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -349,12 +349,14 @@ CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[]) cdo_percentage float, wap_basis float, portfolio_spread float, subordination float, thickness float ) AS $$ BEGIN - RETURN QUERY SELECT a.Orig_Moody, a.Curr_Moody, NULL, b."Deal Name", b.manager,NULL, + RETURN QUERY SELECT a.Orig_Moody, a.Curr_Moody, NULL, b."Deal Name", b.manager, NULL, a.Spread/100, b."Deal Issue Date", b."Reinv End Date", b.Maturity, - CAST(NULL AS float), b."CDO Percentage"/100, CAST(NULL AS FLOAT), CAST(NULL AS float), + e.stalepercentage, greatest(b."CDO Percentage"/100, e.cdopercentage), CAST(NULL AS float), e.dealspread, d.subordination, d.thickness FROM cusip_universe a LEFT JOIN latest_clo_universe b ON a.dealname = b.dealname + LEFT JOIN et_deal_model_numbers e + ON a.dealname = e.dealname RIGHT JOIN (SELECT unnest($1) AS cusip, generate_subscripts(p_cusips, 1) AS id) c ON c.cusip = a.cusip LEFT JOIN et_model_numbers d ON d.cusip = c.cusip ORDER BY c.id; END; @@ -426,12 +428,22 @@ CREATE OR REPLACE FUNCTION et_deal_subordination(p_dealname varchar(10)) END; $$ LANGUAGE plpgsql; -CREATE TABLE et_model_numbers( + +CREATE TABLE et_deal_model_numbers( + dealname varchar(10), + cdopercentage float, + stalepercentage float, + dealspread float, + PRIMARY KEY(dealname) + ); + +CREATE TABLE et_cusip_model_numbers( Cusip varchar(9) references cusip_universe(Cusip), + dealname varchar(10), subordination float, thickness float, - wap_basis float, PRIMARY KEY(Cusip) ); -GRANT ALL ON et_model_numbers TO et_user; +GRANT ALL ON et_deal_model_numbers TO et_user; +GRANT ALL ON et_cusip_model_numbers TO et_user; |