select sum(currentbalance* coalesce(b.bid,c.price))/sum(currentbalance) as wap, sum(currentbalance) as pricedbalance from et_collateral a 
	left join latest_markit_prices b on a.loanxid=b.loanxid
	left join bloomberg_prices c on a.cusip=c.cusip
	where a.dealname='octagon8' and coalesce(b.bid,c.price) is not Null;

select issuername, sum(currentbalance) AS currentbalance, a.maturity, fixedorfloat, 
	avg(coalesce((b.bid+b.offer)/2, c.price)) as price, avg(grosscoupon) as grosscoupon,
	avg(a.spread) as grossmargin, 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(defaultedflag) as defaultedflag 
	from et_collateral  a  left join latest_markit_prices b on a.loanxid=b.loanxid left join bloomberg_prices c
	on a.cusip=c.cusip where a.dealname='babs062' 
group by issuername, a.maturity, a.loanxid, fixedorfloat, a.frequency, a.cusip order by issuername;

select issuername, sum(currentbalance) AS currentbalance, a.maturity, fixedorfloat, a.dealname,
	avg(coalesce((b.bid+b.offer)/2, c.price)) as price, avg(grosscoupon) as grosscoupon,
	avg(a.spread) as grossmargin, 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(defaultedflag) as defaultedflag 
	from et_collateral  a  left join latest_markit_prices b on a.loanxid=b.loanxid left join bloomberg_corp c
	on a.cusip=c.cusip
group by issuername, a.dealname, a.maturity, a.loanxid, fixedorfloat, a.frequency, a.cusip order by dealname;

select * from latest_markit_prices;
select * from bloomberg_prices where cusip='038521AG5';
select distinct cusip from et_collateral;
select count(a.currentbalance) from et_collateral a where dealname='octagon8';
select dealname from clo_universe;

--query latest prices from markit_prices
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 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;

select * from dealcusipmapping;

select * from et_aggdealinfo('octagon8');

select * from et_ReadMapped('abcde',2016-4-12,3);

insert into et_user_mapping (issuername, maturity, grosscoupon, spread, loanxid) VALUES ('Aramark','1/24/2014',2.34,1.88,'LX063469');

select * from cusip_user_mapping;


select * from et_collateral where;

select * from latest_markit_prices where issuer like '%Acxiom%';

insert into loanx_user_mapping (issuername, maturity, spread, loanxid) VALUES ('RH Donnelley','10/24/2014','4.25','LX113368');
insert into loanx_user_mapping (issuername, maturity, spread, loanxid) VALUES ('RH Donnelley','10/24/2014','5.25','LX213368');
update loanx_user_mapping SET issuername = 'RH Donnelley', maturity = '10/24/2014', spread = '4.25', loanxid = 'LX113368' WHERE issuername = 'RH Donnelley' and maturity = '10/24/2014'and spread = '4.25'and loanxid = 'LX113368'

select * from loanx_user_mapping;
delete from loanx_user_mapping;

select distinct loanxid,cusip from et_collateral where loanxid is not NULL and cusip is not NULL;

select * from bloomberg_corp;

select * from et_latestdealinfo('abrlf','8/24/2012');

UPDATE et_collateral a SET et_loanxid = 'LX050789' WHERE  a.dealname = 'abrlf' and a.name = 'AVIO S.p.A - Term Loan B2' and a.maturity = '2014-12-13';
select * from et_collateral where dealname = 'abrlf' and et_loanxid= 'LX050789';
select * from et_collateral where dealname = 'abrlf';

Drop function et_aggdealinfo_historical (varchar(10), date);
select count(*) from loanx_user_mapping a where a.issuername = 'RH Donnelley' and a.maturity = '10/24/2014' and a.spread = '4.25'
count(loanxid) from loanx_user_mapping a where a.issuername = 'RH Donnelley' and a.maturity = '10/24/2014' and a.spread = '4.25'

CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_date date)
       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, et_loanxid varchar(10), et_cusip varchar(9)) 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)) 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, a.et_loanxid, a.et_cusip from et_latestdealinfo(p_dealname, p_date) a
	left join historical_markit_prices(p_date) b on b.loanxid=Coalesce(a.loanxid,a.et_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;

select * from et_aggdealinfo_historical('abrlf', '8/27/2012');

CREATE TABLE loanx_user_mapping (
       IssuerName text,
       Maturity date,
       Spread float default NULL,
       LoanxID varchar(8) default NULL
);

Drop TABLE loanx_user_mapping;