diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/et_tables.sql | 68 |
1 files changed, 43 insertions, 25 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index e1647940..eff848f4 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -310,29 +310,25 @@ AS $$ return price1 if pricedt1> pricedt2 else price2 $$ LANGUAGE plpythonu; -CREATE OR REPLACE FUNCTION et_aggdealinfo (p_dealname varchar(10)) - RETURNS TABLE(issuername text, currentbalance float, maturity date, - fixedorfloat intex_fixedorfloat, assettype text, price float, grosscoupon float, spread float, - frequency varchar(1), nextpaydate date, loanxid varchar(10), cusip varchar(9), - covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean) AS $$ +CREATE OR REPLACE FUNCTION et_historicaldealinfo (p_dealname varchar(10), 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.issuername, sum(a.currentbalance) AS - currentbalance, a.maturity, a.fixedorfloat, max(a.assettype), - avg(coalesce((b.bid+b.offer)/2, c.price)) AS price, - avg(a.grosscoupon) AS grosscoupon, avg(a.spread) AS - spread, a.frequency, min(a.nextpaydate) AS nextpaydate, - a.loanxid, a.cusip, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or - 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_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; + SELECT max(et_collateral.updatedate) INTO latestdate FROM et_collateral + WHERE et_collateral.dealname = p_dealname + AND et_collateral.updatedate <= p_date; + RETURN QUERY SELECT a.* FROM et_collateral a WHERE a.dealname = p_dealname AND + a.updatedate=latestdate ORDER BY lower(a.issuername); END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION et_latestdealinfo (p_dealname varchar(10), p_date date) +CREATE OR REPLACE FUNCTION et_latestdealinfo (p_dealname varchar(10)) 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, @@ -343,13 +339,33 @@ CREATE OR REPLACE FUNCTION et_latestdealinfo (p_dealname varchar(10), p_date dat DECLARE latestdate date; BEGIN SELECT max(et_collateral.updatedate) INTO latestdate FROM et_collateral - WHERE et_collateral.dealname = p_dealname - AND et_collateral.updatedate <= p_date; + WHERE et_collateral.dealname = p_dealname; RETURN QUERY SELECT a.* FROM et_collateral a WHERE a.dealname = p_dealname AND a.updatedate=latestdate ORDER BY lower(a.issuername); END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION et_aggdealinfo (p_dealname varchar(10)) + RETURNS TABLE(issuername text, currentbalance float, maturity date, + fixedorfloat intex_fixedorfloat, assettype text, price float, grosscoupon float, spread float, + frequency varchar(1), nextpaydate date, loanxid varchar(10), cusip varchar(9), + covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean) AS $$ + BEGIN + RETURN QUERY SELECT a.issuername, sum(a.currentbalance) AS + currentbalance, a.maturity, a.fixedorfloat, max(a.assettype), + avg(coalesce((b.bid+b.offer)/2, c.price, a.intexprice)) AS price, + avg(a.grosscoupon) AS grosscoupon, avg(a.spread) AS + spread, a.frequency, min(a.nextpaydate) AS nextpaydate, + a.loanxid, a.cusip, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or + 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_latestdealinfo(p_dealname) a + 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; + END; + $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION et_ReadMapped(p_issuername text, p_maturity date, p_spread float) RETURNS TABLE(loanxid varchar(10), cusip varchar(9)) AS $$ @@ -408,10 +424,12 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_ a.loanxid, a.cusip, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or 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_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, + bool_or(a.defaultedflag) AS defaultedflag, a.et_loanxid, a.et_cusip + FROM et_historicaldealinfo(p_dealname, p_date) a + 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; END; $$ LANGUAGE plpgsql; |