diff options
| -rw-r--r-- | sql/et_tables.sql | 31 |
1 files changed, 7 insertions, 24 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index b71141b8..f0cfbee9 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -204,7 +204,7 @@ CREATE TABLE bloomberg_corp_old ( ); CREATE TABLE bloomberg_corp_ref ( - id_bb_unique text PRIMARY KEY, + figi varchar(12) PRIMARY KEY, cusip varchar(9), Issuer text, Maturity date, @@ -223,18 +223,19 @@ CREATE TABLE bloomberg_corp_ref ( called boolean, called_date date, status bloomberg_ln_status, - loanxid varchar(8)[]); + loanxid varchar(8)[], + reset_idx text); GRANT ALL ON bloomberg_corp_ref TO et_user; CREATE TABLE bloomberg_corp( - id_bb_unique text REFERENCES bloomberg_corp_ref, + figi varchar(12) REFERENCES bloomberg_corp_ref, pricingdate date, price float, loan_margin float, amount_outstanding float, source bloomberg_source, - PRIMARY KEY (id_bb_unique, pricingdate)); + PRIMARY KEY (figi, pricingdate)); GRANT ALL ON bloomberg_corp TO et_user; @@ -508,27 +509,9 @@ CREATE OR REPLACE FUNCTION et_ReadMapped(p_issuername text, p_maturity date, p_s $$ LANGUAGE plpgsql; CREATE OR REPLACE VIEW latest_bloomberg_corp AS -SELECT b.pricingdate, CASE - WHEN c.cusip is NULL THEN substring(id_bb_unique from 3)::varchar(9) - ELSE c.cusip - END, b.price, c.issuer, c.issue_size, - b.amount_outstanding, c.covlite, c.secondlien, c.defaulted FROM - (SELECT MAX(pricingdate) AS pricingdate, id_bb_unique FROM bloomberg_corp - GROUP BY id_bb_unique) a - JOIN bloomberg_corp b USING (id_bb_unique, pricingdate) JOIN bloomberg_corp_ref c - USING (id_bb_unique); - -GRANT ALL ON latest_bloomberg_corp TO et_user; - -CREATE OR REPLACE VIEW latest_bloomberg_corp2 AS -SELECT b.pricingdate, id_bb_unique, b.price, b.loan_margin, b.source, c.issuer, c.issue_size, - b.amount_outstanding, c.covlite, c.secondlien, c.defaulted FROM - (SELECT MAX(pricingdate) AS pricingdate, id_bb_unique FROM bloomberg_corp - GROUP BY id_bb_unique) a - JOIN bloomberg_corp b USING (id_bb_unique, pricingdate) JOIN bloomberg_corp_ref c - USING (id_bb_unique); +SELECT DISTINCT ON (figi) * FROM bloomberg_corp_ref JOIN +bloomberg_corp USING (figi) ORDER BY figi, pricingdate DESC; -GRANT ALL ON latest_bloomberg_corp2 TO et_user; CREATE OR REPLACE FUNCTION historical_bloomberg_corp(p_date date) RETURNS SETOF latest_bloomberg_corp AS $$ |