diff options
| -rw-r--r-- | sql/et_tables.sql | 18 |
1 files changed, 13 insertions, 5 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 170c2583..edf1551b 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -373,11 +373,7 @@ CREATE OR REPLACE FUNCTION historical_clo_universe(p_dealname varchar(10), p_dat $$ LANGUAGE plpgsql; 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, "Bloomberg Ticker" text) AS $$ + RETURNS SETOF cusip_universe AS $$ DECLARE latestdate date; BEGIN SELECT max(cusip_universe.updatedate) INTO latestdate FROM cusip_universe @@ -387,6 +383,16 @@ CREATE OR REPLACE FUNCTION historical_cusip_universe(p_cusip varchar(9), p_date END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION historical_dealname_universe(p_dealname varchar(10), p_date date) + RETURNS SETOF cusip_universe AS $$ + DECLARE latestdate date; + BEGIN + SELECT max(cusip_universe.updatedate) INTO latestdate FROM cusip_universe + WHERE cusip_universe.dealname = p_dealname AND cusip_universe.updatedate<=p_date; + RETURN QUERY SELECT a.* FROM cusip_universe a WHERE a.dealname=p_dealname + AND a.updatedate=latestdate; + END; +$$ LANGUAGE plpgsql; CREATE VIEW dealcusipmapping AS SELECT dealname, cusip from latest_cusip_universe; @@ -660,6 +666,8 @@ CREATE TABLE et_cusip_model_numbers( duration float, delta float, updatedate date, + mvoc float, + mvcoverage float, PRIMARY KEY(Cusip, updatedate) ); |