Hello, I face issue with table with inconsistent values in one column. There are orders in our local currency and orders in USD currency. Result of the report will be grouped informations about clients ordering between @startdate and @enddate parameters. Problem is, that result has to be in one currency (one client could have done orders). Exchange rate is each day different and I want to let report user to set parameter @Exchange_rate. Problem is, that I dont know how to multiply USD values with @Exchange_rate parameter value. Code below is not working. CASE WHEN Currency = 'USD' THEN Order_value*@Exchange_rate ELSE Order_value END Order_value_local_currency Is it possible to do it somehow? I dont want to have fixed exchange rate and I also dont want to be asked each week for correction of that value.
Two alternatives: (1) Add a tblExchangeRateHistory table with the daily exchange rates. (2) Add a column to your table where the exchange rate will be stored. If your table is getting thousands of rows added daily, the first option is probably preferred.