diff options
| -rw-r--r-- | sql/et_tables.sql | 8 |
1 files changed, 4 insertions, 4 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 03562d90..9defe3b5 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -338,14 +338,14 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo (p_dealname varchar(10)) 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(coalesce((b.bid+b.offer)/2, c.price, a.userprice, 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 latest_markit_prices2 b ON coalesce(a.loanxid, a.ET_LoanXID) = b.loanxid LEFT JOIN bloomberg_corp c ON a.cusip=c.cusip WHERE a.ReinvFlag IS NOT TRUE GROUP BY a.issuername, a.maturity, a.loanxid, a.fixedorfloat, a.frequency, a.cusip ORDER BY issuername; @@ -409,7 +409,7 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_ 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(coalesce((b.bid+b.offer)/2, c.price, a.userprice, 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 @@ -417,7 +417,7 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_ 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_historicaldealinfo(p_dealname, p_date) a - LEFT JOIN historical_markit_prices2(p_date) b ON a.loanxid=b.loanxid + LEFT JOIN historical_markit_prices2(p_date) b ON coalesce(a.loanxid, a.ET_LoanXID)=b.loanxid LEFT JOIN historical_bloomberg_corp(p_date) c ON a.cusip=c.cusip WHERE a.ReinvFlag IS NOT TRUE GROUP BY a.issuername, a.maturity, a.loanxid, a.fixedorfloat, |