diff options
Diffstat (limited to 'sql/et_tables.sql')
| -rw-r--r-- | sql/et_tables.sql | 15 |
1 files changed, 13 insertions, 2 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 999120ab..7503521d 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -355,7 +355,7 @@ CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[]) d.subordination, d.thickness FROM cusip_universe a LEFT JOIN latest_clo_universe b ON a.dealname = b.dealname - LEFT JOIN et_deal_model_numbers e + LEFT JOIN latest_deal_model_numbers e ON a.dealname = e.dealname RIGHT JOIN (SELECT unnest($1) AS cusip, generate_subscripts(p_cusips, 1) AS id) c ON c.cusip = a.cusip LEFT JOIN et_model_numbers d ON d.cusip = c.cusip ORDER BY c.id; @@ -434,7 +434,9 @@ CREATE TABLE et_deal_model_numbers( cdopercentage float, stalepercentage float, dealspread float, - PRIMARY KEY(dealname) + marketvalue float, + updatedate date, + PRIMARY KEY(dealname, updatedate) ); CREATE TABLE et_cusip_model_numbers( @@ -447,3 +449,12 @@ CREATE TABLE et_cusip_model_numbers( GRANT ALL ON et_deal_model_numbers TO et_user; GRANT ALL ON et_cusip_model_numbers TO et_user; + + +CREATE VIEW latest_deal_model_numbers AS + SELECT b.* + FROM (SELECT MAX(updatedate) AS latestdate, dealname FROM et_deal_model_numbers GROUP BY dealname) a + JOIN et_deal_model_numbers b ON a.dealname = b.dealname AND a.latestdate= b.updatedate + ORDER by dealname asc; + +GRANT ALL ON latest_deal_model_numbers TO et_user; |