diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/et_tables.sql | 32 |
1 files changed, 26 insertions, 6 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index eff848f4..a26ad542 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -260,7 +260,10 @@ CREATE TABLE cusip_universe ( Curr_Detach float, Floater_Index text, Spread float, - PRIMARY KEY(Cusip) + subordination float, + thickness float, + updatedate date, + PRIMARY KEY(Cusip, updatedate) ); GRANT ALL ON cusip_universe TO et_user; @@ -273,6 +276,13 @@ CREATE VIEW latest_clo_universe AS GRANT ALL ON latest_clo_universe TO et_user; +CREATE VIEW latest_cusip_universe AS + SELECT b.* + FROM (SELECT MAX(updatedate) AS latestdate, cusip FROM cusip_universe GROUP BY cusip) a + JOIN cusip_universe b ON a.cusip = b.cusip AND a.latestdate= b.updatedate + ORDER by cusip asc; + +GRANT ALL ON latest_cusip_universe TO et_user; CREATE VIEW dealcusipmapping AS SELECT dealname, unnest("Deal Cusip List") AS Cusip from latest_clo_universe; @@ -547,11 +557,13 @@ CREATE TABLE et_deal_model_numbers( ); CREATE TABLE et_cusip_model_numbers( - Cusip varchar(9) references cusip_universe(Cusip), - dealname varchar(10), - subordination float, - thickness float, - PRIMARY KEY(Cusip) + Cusip varchar(9), + price float, + wal float, + duration float, + delta float, + updatedate date, + PRIMARY KEY(Cusip, updatedate) ); GRANT ALL ON et_deal_model_numbers TO et_user; @@ -565,3 +577,11 @@ CREATE VIEW latest_deal_model_numbers AS ORDER by dealname asc; GRANT ALL ON latest_deal_model_numbers TO et_user; + +CREATE VIEW latest_cusip_model_numbers AS + SELECT b.* + FROM (SELECT MAX(updatedate) AS latestdate, cusip FROM et_cusip_model_numbers GROUP BY cusip) a + JOIN et_cusip_model_numbers b ON a.cusip = b.cusip AND a.latestdate = b.updatedate + ORDER by cusip asc; + +GRANT ALL ON latest_cusip_model_numbers TO et_user; |