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.