diff options
Diffstat (limited to 'sql/et_tables.sql')
| -rw-r--r-- | sql/et_tables.sql | 74 |
1 files changed, 64 insertions, 10 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index d842249a..78d660b6 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -81,7 +81,7 @@ CREATE TABLE markit_prices2 ( PricingDate date, PRIMARY KEY (LoanXID, PricingDate) ); - + ALTER TABLE markit_prices2 OWNER TO et_user; CREATE TABLE markit_facility ( @@ -104,12 +104,48 @@ CREATE TABLE markit_facility ( ALTER TABLE markit_facility OWNER TO et_user; CREATE VIEW latest_markit_facility AS - SELECT b.* + 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 OR REPLACE FUNCTION historical_facility(p_date date) + RETURNS TABLE(p_loanxid varchar(8), pmdid float(10), issuername varchar(100), + dealname varchar(60), facility_type varchar(40), loanx_facility_type varchar(50), + initial_amount float(15), initial_spread float(7), maturity date, industry varchar(50), + loanxstatus varchar(1), created_time date, p_modified_time date) AS $$ + DECLARE latestdate date; + BEGIN + RETURN QUERY SELECT b.* + FROM (SELECT MAX(modified_time) AS latestdate, loanxid + FROM markit_facility WHERE modified_time <= p_date GROUP BY loanxid) a + JOIN markit_facility b ON a.loanxid = b.loanxid AND a.latestdate = b.modified_time; + END; + $$ LANGUAGE plpgsql; + +ALTER FUNCTION historical_facility(p_date date) + OWNER TO et_user; + +CREATE OR REPLACE FUNCTION et_historical_collateral(p_date date) + RETURNS TABLE(dealname varchar(8), updatedate date, name text, issuername text, + currentbalance float, maturity date, assetsubtype varchar(10), assettype varchar(25), + grosscoupon float, spread float, frequency varchar(1), nextpaydate date, + secondlien boolean, loanxid varchar(10), et_loanxid varchar(10), cusip varchar(9), + et_cusip varchar(10), intexprice float, intexpricesource text, intexpricedate date, + userprice float, userpricedate date, fixedorfloat intex_fixedorfloat, + defaultedflag boolean, covlite text, iscdo boolean) AS $$ + DECLARE latestdate date; + BEGIN + RETURN QUERY SELECT a.* FROM (SELECT max(et_collateral.updatedate) AS latestdate,et_collateral.dealname + FROM et_collateral WHERE updatedate <= p_date GROUP BY et_collateral.dealname) b + JOIN et_collateral a ON a.dealname=b.dealname AND a.updatedate=b.latestdate; + END; + $$ LANGUAGE plpgsql; + +ALTER FUNCTION et_historical_collateral(p_date date) + OWNER 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 @@ -119,6 +155,26 @@ CREATE VIEW latest_markit_prices2 AS GRANT ALL ON latest_markit_prices2 TO et_user; +CREATE OR REPLACE FUNCTION historical_markit_prices2(p_date date) + RETURNS TABLE(loanxid varchar(8), issuername varchar(100), dealname varchar(60), + facility_type varchar(40), loanx_facility_type varchar(50), initial_amount float(15), + initial_spread float(7), maturity date, industry varchar(5), + bid float, offer float, depth integer, pricing_date date) AS $$ + DECLARE latestdate date; + BEGIN + RETURN QUERY SELECT c.p_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, markit_prices2.loanxid FROM markit_prices2 + WHERE pricingdate <= p_date GROUP BY markit_prices2.loanxid) a + JOIN markit_prices2 b ON a.loanxid = b.loanxid AND a.latestdate = b.pricingdate + JOIN historical_facility(p_date) c ON c.p_loanxid = a.loanxid; + END; + $$ LANGUAGE plpgsql; + +ALTER FUNCTION historical_markit_prices2(p_date date) + OWNER TO et_user; + CREATE TYPE bloomberg_source AS ENUM('TRAC', 'BGN', 'MSG1', 'BVAL', 'EXCH', 'BCMP', 'LCPR', 'BFV'); CREATE TYPE bloomberg_cpntype AS ENUM('FIXED', 'FLOATING', 'EXCHANGED', 'DEFAULTED', @@ -215,7 +271,9 @@ CREATE VIEW latest_clo_universe AS JOIN clo_universe b ON a.dealname = b.dealname AND a.latestdate= b."Latest Update" ORDER by dealname asc; -GRANT ALL ON latest_clo_universe TO et_user; +-- CREATE OR REPLACE FUNCTION historical_clo_universe(p_date date) +-- RETURNS TABLE(dealname varchar(8), "Deal Name" text, varchar +-- GRANT ALL ON latest_clo_universe TO et_user; CREATE VIEW dealcusipmapping AS SELECT dealname, unnest("Deal Cusip List") AS Cusip from latest_clo_universe; @@ -227,8 +285,6 @@ CREATE VIEW latest_markit_prices AS FROM (SELECT MAX(pricingdate) AS latestdate, loanxid FROM markit_prices GROUP BY loanxid) a JOIN markit_prices b ON a.loanxid = b.loanxid AND a.latestdate= b.pricingdate; -GRANT ALL ON latest_markit_prices TO et_user; - CREATE OR REPLACE FUNCTION et_historical_collateral(p_date date) RETURNS TABLE(dealname varchar(8), updatedate date, name text, issuername text, currentbalance float, maturity date, assetsubtype varchar(10), assettype varchar(25), @@ -240,7 +296,7 @@ CREATE OR REPLACE FUNCTION et_historical_collateral(p_date date) DECLARE latestdate date; BEGIN RETURN QUERY SELECT a.* FROM (SELECT max(et_collateral.updatedate) AS latestdate,et_collateral.dealname - FROM et_collateral GROUP BY et_collateral.dealname) b + FROM et_collateral WHERE updatedate <= p_date GROUP BY et_collateral.dealname) b JOIN et_collateral a ON a.dealname=b.dealname AND a.updatedate=b.latestdate; END; $$ LANGUAGE plpgsql; @@ -314,9 +370,7 @@ CREATE OR REPLACE FUNCTION historical_markit_prices(p_date date) industry text, sp varchar(4), moodys varchar(4), amount float, maturity date, spread float, bid float, offer float, depth integer, stm float, pricingdate date) AS $$ BEGIN - RETURN QUERY SELECT b.loanxid, b.issuer, b.dealname, b.facility, b.industry, b.sp, - b.moodys, b.amount, b.maturity, b.spread, b.bid, b.offer, b.depth, b.stm, - a.latestdate + RETURN QUERY SELECT b.* FROM (SELECT MAX(c.pricingdate) AS latestdate, c.loanxid FROM markit_prices c WHERE c.pricingdate<=p_date GROUP BY c.loanxid) a JOIN markit_prices b ON a.loanxid = b.loanxid AND a.latestdate= b.pricingdate; @@ -362,7 +416,7 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_ 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, a.et_loanxid, a.et_cusip from et_latestdealinfo(p_dealname, p_date) a - left join historical_markit_prices(p_date) b on a.loanxid=b.loanxid left + left join historical_markit_prices2(p_date) b on a.loanxid=b.loanxid left join historical_bloomberg_corp(p_date) 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, a.et_loanxid, a.et_cusip ORDER BY issuername; |