library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
dbCon <- dbConnect(drv, dbname="ET", user="et_user", password="Serenitas;1")
dealnames <- dbGetQuery(dbCon, "select dealname from clo_universe")
pricingcoverage <- c()
for (dealname in dealnames$dealname){
    r <- dbGetQuery(dbCon, paste(
                        paste("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_corp c on a.cusip=c.cusip",
                         "where a.dealname='"),
                        dealname,
                        "' and coalesce(b.bid,c.price) is not Null",sep=""))
    s <- dbGetQuery(dbCon,
                 paste("select sum(currentbalance) as totalbalance from et_collateral where dealname='",dealname,"'",sep=""))
    pricingcoverage <- rbind(pricingcoverage, c(dealname,r$wap,r$pricedbalance/s$totalbalance))
}
pricingcoverage <- data.frame(dealname=dealnames$dealname,wap=as.numeric(pricingcoverage[,2]), coverage = as.numeric(pricingcoverage[,3]))

dbDisconnect(dbCon)