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;