exchange rate calculation on Master calendar table

[grajmca_sgp123]

Hi All,

I have two tables Orders and Currency table. Orders values are in local currency format, I want to convert this local currency to USD format by using Currency table. Here below are the sample tables

Orders:  we have values for every date

OrderDate	Sales	Local Currency
6/15/2014	100	EUR
8/22/2014	120	EUR
9/14/2014	80	EUR
11/11/2014	200	EUR
12/2/2014	150	EUR
1/14/2015	300	EUR
2/28/2015	50	EUR
6/25/2015	70	EUR
8/15/2015	100	EUR

Currency table: we have exchange rate values for every quarter.

From Date	Currency	Exachange rate
6/2/2014	EUR	0.55
9/1/2014	EUR	1.24
12/1/2014	EUR	0.00123
3/1/2015	EUR	0.34
6/1/2015	EUR	0.5

I want to join these tables based on From Date and Order Date fields, but I do not have all FromDate values in Currency table  so we need to create master calendar data for From Date. I am fine with Master Calendar table I know how to create it but my requirement is ,if OrderDate fall between FromDate quarter dates i need to pick that date related exchange rate and multiple with sales.

Example:

OrderDate	Sales	Local Currency
6/15/2014	100	EUR
8/22/2014	120	EUR
9/14/2014	80	EUR
11/11/2014	200	EUR
12/2/2014	150	EUR
1/14/2015	300	EUR
2/28/2015	50	EUR
6/25/2015	70	EUR
8/15/2015	100	EUR

for example in above table first order(6/15/2015) fall between 6/2/2014 and 9/31/2014(From date field),So i need to pick exchange rate related to FromDate quarter and calculate sales like Sum(100 * 0.55)

For 3rd order(9/14/2014) fall between 9/1/2014 and 12/1/2014. So sum value is Sum(80*1.24)

same way for other date also.

output looks like this.

OrderDate	Sales
6/15/2014	100*0.55
8/22/2014	120*0.55
9/14/2014	80*1.24
11/11/2014	200*	1.24
12/2/2014	150	*0.00123
1/14/2015	300	* 0.00123
2/28/2015	50	* 0.00123
6/25/2015	70	*.05
8/15/2015	100	*.50

please let me know your inputs here,Thanks in advance

[grajmca_sgp123]

excellent Sergey you are Genius Thank you so much for your hard work