diff options
Diffstat (limited to 'sql/et_tables.sql')
| -rw-r--r-- | sql/et_tables.sql | 41 |
1 files changed, 23 insertions, 18 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 0ad3f255..3713d535 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -284,14 +284,9 @@ CREATE TABLE deal_indicative ( "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 + paid_down date, ); GRANT ALL ON deal_indicative TO et_user; @@ -307,7 +302,13 @@ CREATE TABLE clo_universe ( "Defaulted Bal" float, "Curr Coupon" float, "Latest Update" date, - PRIMARY KEY (dealname, "Latest Update") + latest_refi_date date, + "Deal Cusip List" text[], + maturity date, + reinv_end_date date, + orig_deal_bal float, + orig_collat_bal float, + PRIMARY KEY (dealname, "Latest Update"), ); GRANT ALL ON clo_universe TO et_user; @@ -371,14 +372,15 @@ CREATE OR REPLACE VIEW cusip_universe AS JOIN cusip_update b USING (cusip); CREATE OR REPLACE VIEW latest_clo_universe AS - 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 + SELECT b.dealname, "Deal Name", Manager, b.orig_deal_bal, b."Curr Deal Bal", + b.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, b.maturity, b.reinv_end_date, b."Latest Update", + b.last_refi_date, b."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" JOIN deal_indicative ON b.dealname = deal_indicative.dealname - ORDER BY dealname asc; + ORDER BY dealname ASC; GRANT ALL ON latest_clo_universe TO et_user; @@ -399,12 +401,15 @@ CREATE OR REPLACE FUNCTION historical_clo_universe(p_dealname varchar(10), p_dat 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; + RETURN QUERY SELECT a.dealname, "Deal Name", Manager, a.orig_deal_bal, a."Curr Deal Bal", + a.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, a.maturity, a.reinv_end_date, a."Latest Update", + a.last_refi_date, a."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; |