1 files changed, 37 insertions, 0 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql
index d0ec4bd4..93d80f96 100644
--- a/sql/et_tables.sql
+++ b/sql/et_tables.sql
@@ -270,6 +270,43 @@ 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) 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;
+       END;
+$$ LANGUAGE plpgsql;
+
+GRANT ALL ON historical_clo_universe TO et_user;
+
+CREATE OR REPLACE FUNCTION historical_cusip_universe(p_cusip varchar(9), p_date date)
+       RETURNS TABLE(cusip varchar(9), isin varchar(12), dealname varchar(10), tranche text,
+       coupon float, orig_balance float, curr_balance float, factor float, orig_moody text,
+       curr_moody text, orig_attach float, orig_detach float, curr_attach float,
+       curr_detach float, floater_index text, spread float, subordination float,
+       thickness float, updatedate date) AS $$
+       DECLARE latestdate date;
+       BEGIN
+        SELECT max(cusip_universe.updatedate) INTO latestdate FROM cusip_universe
+	WHERE cusip_universe.cusip = p_cusip AND cusip_universe.updatedate<=p_date;
+	RETURN QUERY SELECT a.* FROM cusip_universe a WHERE a.cusip=p_cusip
+	AND a.updatedate=latestdate;
+       END;
+$$ LANGUAGE plpgsql;
+
+GRANT ALL ON historical_cusip_universe TO et_user;
+
+
 -- CREATE VIEW dealcusipmapping AS
 --        SELECT dealname, unnest("Deal Cusip List") AS Cusip from latest_clo_universe;