如何获得转换后的列值

Question

我们的数据库只提供基于cad的汇率( FX /cad)，但是，我需要使用基于美元的汇率(fx/美元)，所以我想这样做。

基于FXrate_cad_基于FXrate_美元_to_cad的spotrate

SELECT npv.ValDate, month(npv.ValDate) as Month, rtrim(npv.CurID) as CurID, (npv.SpotRate) as SpotRate_Cad  
 FROM DB.NPVPLFxRate as npv 
 WHERE (npv.ValDate<='2017-03-23')  
 AND (npv.ValDate>='2016-11-01')    
 LEFT JOIN  
 (SELECT npv.ValDate as usdValDate, (npv.SpotRate) as SpotRate_usd  
 FROM DB.NPVPLFxRate as npv 
 WHERE (npv.ValDate<='2017-03-23')  
 AND (npv.ValDate>='2016-11-01')    
 AND (npv.CurID = "usd"))  usdFX    
 ON npv.ValDate = usdFX.usdValDate  
 ORDER BY npv.ValDate, npv.CurID    

Answer 1

试试这个：

SELECT 
  cad.ValDate, 
  month(cad.ValDate) as Month, 
  rtrim(cad.CurID) as CurID, 
  cad.SpotRate as SpotRate_Cad,
  cad.SpotRate / usd.SpotRate as SpotRate_USD
FROM DB.NPVPLFxRate as cad 
JOIN DB.NPVPLFxRate as usd ON usd.ValDate = cad.ValDate
WHERE cad.ValDate between '2016-02-28' and '2016-03-02' and usd.CurID = 'usd'
ORDER BY cad.ValDate, cad.CurID 

我不确定您是否应该考虑LEFT JOIN，因为您的费率表应该有每天完整的信息。