sql query to fetch exchange rate

Given a sales table with sales amount stored in local currencies and an exchange rate table containing currency conversion rate, to get total sales amount in USD for each sales date I need query

Sales Table:

Sales Date  Sales Amount    Currency       
01-JAN-16            500    INR    
01-JAN-16            100    GBP    
02-JAN-16           1000    INR    
02-JAN-16            150    GBP    
03-JAN-16           1500    INR  

Exchange Rate Table:

Source Currency  Target Currency  Exchange Rate  Effective Start Date
INR              USD                      0.014  31-DEC-15     
INR              USD                      0.015  02-JAN-16     
GBP              USD                       1.32  20-DEC-15     
GBP              USD                       1.30  01-JAN-16     
GBP              USD                       1.35  10-JAN-16   

have no idea how I should proceed

I have to do two things match the currency and then check for the same date exchange rate or before sales_date

Answer

Try this:

with SOURCE as
(
select s1.*, coalesce(e1.Rate, 1) as ExRate, row_number() over(partition by e2.Source order by e2.StartDate desc) as r_num
from Sales s1
left join Exchange e2
on s1.Currency = e2.Source
and e2.StartDate <= s1.SalesDate 
)
select SOURCE.*, SalesAmount*ExRate as USDAmount
from SOURCE 
where r_num = 1