I am trying to write a query that tries to extract the value of the exchange rate where:
- Query date = the exchange date; and
- Query currency = the specified currency
If it does not contain a matching value like on 5-Sep-2015, it should extract the value from the last date that contains a value. In this case, the value should be taken from 4-Sep-2015:
Create Table ExchRate ( Exch_ID int, Currency varchar(3), Exch_value numeric(14,7), Exch_date datetime) Insert INTO ExchRate Values (1, 'SGD', 1.4173000, '2015-09-04 00:00:00'), (2, 'SGD', 1.4240000, '2015-09-07 00:00:00'), (3, 'SGD', 1.4291500, '2015-09-08 00:00:00'), (4, 'EUR', 0.8984700, '2015-09-04 00:00:00'), (5, 'EUR', 0.8955700, '2015-09-07 00:00:00'), (6, 'EUR', 0.8957000, '2015-09-08 00:00:00')
The result should be:
Currency Exch_Date Exch_Value SGD 2015-09-05 1.4173000
Secondly, what if I am joining from another table to calculate the total cost in USD? What kind of SQL statement should I be using? In this SQLFiddle example, it should have exchange rate value for every PO with different PO dates.