Pastie Support Pastie or read my Pastie << self blog

SELECT a.id, a.bbTicker, a.name, a.isin,
  sum(b.amPurchased*(isNull(CASE WHEN (isNull(a.pxReval,0)<>0) THEN (a.pxReval) ELSE (a.pxMTM) END,b.amPrice)))/sum(b.amPurchased) AS currPrice,
  (eur.rate/c.rate)*(a.amOutstanding/a.amOriginal)*(sum(b.amPurchased*(isNull(CASE WHEN (isNull(a.pxReval,0)<>0) THEN (a.pxReval) ELSE (a.pxMTM) END,b.amPrice)/100.0))/sum(b.amPurchased)) * sum(b.amPurchased) AS amOutstanding,
  CASE WHEN (isNull(walManual,0)<>0) THEN (walManual) ELSE (isNull(a.walNow,CONVERT(float,datediff(d,getdate(),a.dtFinalMat))/365.25)) END AS remainingWAL,
  /*CHECK THIS*/a.margin- ((sum(b.amPurchased*
    (isNull(CASE WHEN (isNull(a.pxReval,0)<>0) THEN (a.pxReval) ELSE (a.pxMTM) END,b.amPrice)-100)*100)/sum(b.amPurchased))
  /CASE WHEN (isNull(walManual,0)<>0) THEN (walManual) ELSE (isNull(a.walNow,CONVERT(float,datediff(d,getdate(),a.dtFinalMat))/365.25))) END AS dmNow,
  '=(RC[-3]*MAX(1,RC[-2]*0.1)*RC[-1])/10000' AS sensitivity
  FROM cecViewPortfolio a RIGHT JOIN bondPrices b ON a.id=b.bondId LEFT JOIN fxRates c ON a.fxId=c.id, fxRates eur
  WHERE isNull(a.amOriginal,0)<>0 AND eur.shortname='EUR' AND isNull(c.rate,0)<>0
  GROUP BY a.id,a.bbTicker,a.name,a.isin, a.amOutstanding, a.amOriginal, c.rate, eur.rate, a.walNow, a.margin, a.dtFinalMat, a.walManual, a.dtWalManual
  HAVING isNull(sum(b.amPurchased),0)<>0