diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/et_tables.sql | 36 |
1 files changed, 33 insertions, 3 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 7503521d..d842249a 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -73,6 +73,17 @@ CREATE TABLE markit_prices ( ALTER TABLE markit_prices OWNER TO et_user; +CREATE TABLE markit_prices2 ( + LoanXID varchar(8), + Bid float, + Offer float, + Depth int, + PricingDate date, + PRIMARY KEY (LoanXID, PricingDate) +); + +ALTER TABLE markit_prices2 OWNER TO et_user; + CREATE TABLE markit_facility ( LoanXID varchar(8), PMDID float(10), @@ -80,14 +91,33 @@ CREATE TABLE markit_facility ( dealname varchar(60), facility_type varchar(40), loanx_facility_type varchar(50), - industry varchar(50), initial_amount float(15), initial_spread float(7), maturity date, + industry varchar(50), + LoanXstatus varchar(1), created_time date, - modified_time date + modified_time date, + PRIMARY KEY (LoanXID, modified_time) ); +ALTER TABLE markit_facility OWNER TO et_user; + +CREATE VIEW latest_markit_facility AS + SELECT b.* + FROM (SELECT MAX(modified_time) AS latestdate, loanxid FROM markit_facility GROUP BY loanxid) a + JOIN markit_facility b ON a.loanxid = b.loanxid AND a.latestdate = b.modified_time; + +GRANT ALL ON latest_markit_facility TO et_user; + +CREATE VIEW latest_markit_prices2 AS + SELECT c.loanxid, c.issuername, c.dealname, c.facility_type, c.loanx_facility_type, + c.initial_amount, c.initial_spread, c.maturity, c.industry, b.bid, b.Offer, b.depth, a.latestdate + FROM (SELECT MAX(pricingdate) AS latestdate, loanxid FROM markit_prices2 GROUP BY loanxid) a + JOIN markit_prices2 b ON a.loanxid = b.loanxid AND a.latestdate= b.pricingdate + JOIN latest_markit_facility c ON a.loanxid = c.loanxid; + +GRANT ALL ON latest_markit_prices2 TO et_user; CREATE TYPE bloomberg_source AS ENUM('TRAC', 'BGN', 'MSG1', 'BVAL', 'EXCH', 'BCMP', 'LCPR', 'BFV'); @@ -240,7 +270,7 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo (p_dealname varchar(10)) bool_or(c.covlite))) AS covlite, (bool_or(a.secondlien) OR bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo, bool_or(a.defaultedflag) AS defaultedflag from et_collateral a - left join latest_markit_prices b on a.loanxid=b.loanxid left + left join latest_markit_prices2 b on a.loanxid=b.loanxid left join bloomberg_corp c on a.cusip=c.cusip where a.dealname = p_dealname group by a.issuername, a.maturity, a.loanxid, a.fixedorfloat, a.frequency, a.cusip ORDER BY issuername; |