1

Hello and thank you for your help I am creating a View in SQL 2008, I need to convert amounts into a different currency, I also need to make sure it is using the appropriate currency based on date. Before I went with just creating an average of the Month to simplify my conversion but I think having each field calculated individually is better.

The Data from my currency table looks like so(simplified) TABLE A. 

HOMECUR  SOURCECUR RATEDATE  RATE 
USD      CAD       20120314  1.0086000
USD      CAD       20120316  1.0087000

The Data from the amount table looks like so(simplified) TABLE B. 

RATEDATE TRANSAMT FISCALYR FISCALPERD ACCTID
20120314 120.00   2012     05         123456

So my question is, what can I put to have the RATE that matches the RATEDATE and if that does not exist the RATEDATE the next RATEDATE just before.

Overall, I am grouping data by acctID, FISCALYR and FISCALPERD and SUMing the conveted amounts, but I decided to convert the amounts before SUMing them with a WITH statement. 

ALTER VIEW vJFTESTVIEW
AS
    WITH        
    ActualRateHAIUSA as(
        SELECT POP =  rat.RATE * act.TRANSAMT,
               act.FISCALYR,
               act.FISCALPERD,
               act.ACCTID as ACID
        FROM 
            TABLEA rat,
            TABLEB act
        WHERE -- as my table shows TABLE B's RATEDATE is on the 15th of March
              --, while TABLE A does not have the 15th of March. 
              --It should then take the one 14h instead 
              --(the next closest date while not going the original date) 
              AND rat.HOMECUR = 'CAD'
              AND rat.SOURCECUR = 'USD' 
    )

Any suggestion would be greatly appreciated.

jnoel10
  • 295
  • 2
  • 4
  • 14
  • What exactly is the question? – LittleBobbyTables - Au Revoir May 03 '13 at 19:39
  • My apologies, I want to know how I can get the accurate RATE from TABLE A. The accurate rate is determined by TABLEB.RATEDATE = TABLEA.RATEDATE OR the RATEDATE just before it. (Updating my question) – jnoel10 May 03 '13 at 19:48
  • You need to create ExchangeRate by Date Ranges & Then sum up the Transaction Amounts by the RateDate. This link should help you: http://stackoverflow.com/questions/2306462/sql-join-against-date-ranges – Akhil May 03 '13 at 19:51
  • Thanks Akhill, I looked at that link before posting this one and it was very confusing to me. I will look into it again and try to make it work for me. – jnoel10 May 03 '13 at 19:58

0 Answers0