diff options
| -rw-r--r-- | sql/et_tables.sql | 61 |
1 files changed, 47 insertions, 14 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index ffbed424..9a85cf17 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -203,7 +203,7 @@ CREATE TABLE bloomberg_mtge ( GRANT ALL ON bloomberg_mtge TO et_user; -CREATE TABLE clo_universe ( +CREATE TABLE clo_universe_old ( dealname varchar(10), "Deal Name" text, Manager text, @@ -228,8 +228,39 @@ CREATE TABLE clo_universe ( PRIMARY KEY (dealname, "Latest Update") ); +CREATE TABLE clo_universe ( + dealname varchar(10), + "Curr Deal Bal" float, + "Curr Collat Bal" float, + "Tranche Factor" float, + "Principal Bal" float, + "Interest Bal" float, + "CDO Percentage" float, + "Defaulted Bal" float, + "Curr Coupon" float, + "Latest Update" date, + PRIMARY KEY (dealname, "Latest Update") +); + GRANT ALL ON clo_universe TO et_user; +CREATE TABLE deal_indicative ( + dealname varchar(10) PRIMARY KEY, + "Deal Name" text, + Manager text, + deal_issue_date date, + maturity date, + pay_day smallint, + reinv_end_date date, + first_pay_date date, + orig_deal_bal float, + orig_collat_bal float, + "Deal Cusip List" text[], + paid_down date +); + +GRANT ALL ON deal_indicative TO et_user; + CREATE TABLE cusip_universe ( Cusip varchar(9), ISIN varchar(12), @@ -257,10 +288,13 @@ CREATE TABLE cusip_universe ( GRANT ALL ON cusip_universe TO et_user; CREATE VIEW latest_clo_universe AS - SELECT b.* + SELECT b.dealname, "Deal Name", Manager, orig_deal_bal, b."Curr Deal Bal", orig_collat_bal, + b."Curr Collat Bal", b."Tranche Factor", b."Principal Bal", b."Interest Bal", b."CDO Percentage", + b."Defaulted Bal", b."Curr Coupon", deal_issue_date, maturity, reinv_end_date, b."Latest Update", + "Deal Cusip List", pay_day, first_pay_date, paid_down FROM (SELECT MAX("Latest Update") AS latestdate, dealname FROM clo_universe GROUP BY dealname) a JOIN clo_universe b ON a.dealname = b.dealname AND a.latestdate= b."Latest Update" - ORDER by dealname asc; + ORDER BY dealname asc; GRANT ALL ON latest_clo_universe TO et_user; @@ -273,19 +307,18 @@ CREATE VIEW latest_cusip_universe AS GRANT ALL ON latest_cusip_universe TO et_user; CREATE OR REPLACE FUNCTION historical_clo_universe(p_dealname varchar(10), p_date date) - RETURNS TABLE(dealname varchar(10), "Deal Name" text, manager text, - "Orig Deal Bal" float, "Curr Deal Bal" float, "Orig Collat Bal" float, - "Curr Collat Bal" float, "Tranche Factor" float, - "Principal Bal" float, "Interest Bal" float, "CDO Percentage" float, - "Defaulted Bal" float, "Curr Coupon" float, "Deal Issue Date" date, - maturity date, "Deal Next Pay Date" date, "Reinv End Date" date, - "Latest Update" date, "Deal Cusip List" text[], paiddown date, "Pay Day" date) AS $$ + RETURNS SETOF latest_clo_universe AS $$ DECLARE latestdate date; BEGIN - SELECT max(clo_universe."Latest Update") INTO latestdate FROM clo_universe - WHERE clo_universe.dealname = p_dealname AND clo_universe."Latest Update"<=p_date; - RETURN QUERY SELECT a.* FROM clo_universe a WHERE a.dealname=p_dealname - AND a."Latest Update"=latestdate; + SELECT max(clo_universe."Latest Update") INTO latestdate FROM clo_universe + WHERE clo_universe.dealname = p_dealname AND clo_universe."Latest Update"<=p_date; + + RETURN QUERY SELECT a.dealname, "Deal Name", Manager, orig_deal_bal, a."Curr Deal Bal", orig_collat_bal, + a."Curr Collat Bal", a."Tranche Factor", a."Principal Bal", a."Interest Bal", a."CDO Percentage", + a."Defaulted Bal", a."Curr Coupon", deal_issue_date, maturity, reinv_end_date, a."Latest Update", + "Deal Cusip List", pay_day, first_pay_date, paid_down FROM clo_universe a + JOIN deal_indicative ON a.dealname = deal_indicative.dealname WHERE a.dealname = p_dealname + AND a."Latest Update" = latestdate; END; $$ LANGUAGE plpgsql; |