SQL - DTS - Rate of exchange and date ranges 1

[gaperry]

I need to update exchange rates in one table based on
values from two other tables using date ranges as shown
below.


Table to be updated:

UPDATE tbl_Sales_Fact

SET Exchange_Rate = 1.448

Exchange Rate comes from another table which could be several currencies
as I have shown here:

1.4485 12/30/2001 GBP
0.884 12/30/2001 EUR
1.41 1/27/2002 GBP
1.4292 2/24/2002 GBP
0.869 1/27/2002 EUR
0.8742 2/24/2002 EUR


The exchange rate for a given month will be applied to the
next month (as shown below) based on a date in the above table


The third table will contain Start and End dates for each month as shown here. (Fiscal Year)

12/31/2001 01/27/2002
01/28/2002 02/24/2002
02/25/2002 03/31/2002
04/01/2002 04/28/2002
04/29/2002 05/26/2002
05/27/2002 06/30/2002
07/01/2002 07/28/2002
07/29/2002 08/25/2002
08/26/2002 09/29/2002
09/30/2002 10/27/2002
10/28/2002 11/24/2002
11/25/2002 12/29/2002


So for example, the above currency of 1.4485 with a date of 12/30/01
would need to fall between the below range shown (Fiscal Year)


WHERE (Currency = 'GBP') AND (Invoice_Date BETWEEN '12-31-01' AND '1-27-02')


This would need to be done for all months in the year for any year.


Thanks

 

[gaperry]

I have been able to accomplish part of the original post with a select statement but I can't get this to work with and UPDATE statement. Here is what I used to select the data from my original post. Any help here would be appreciated. Thanks.


SELECT tbl_final_exchange_rate.cur_exchng_rt
FROM tbl_final_exchange_rate, tbl_fiscalcalendar, tbl_Sales_Fact
WHERE tbl_sales_fact.invoice_date BETWEEN tbl_fiscalcalendar.startdate AND tbl_fiscalcalendar.enddate AND
tbl_Sales_Fact.currency = tbl_final_exchange_rate.from_cur

 

[tlbroadbent]

How do you determine which date range to use from the calendar table? What is the purpose of the date on the Final Exchange Rate table? As the query stands, it will select rows from from tbl_Sales_Fact that match any date range in the calendar. Any row in tbl_Final_Exchange_Rate that matches tbl_Sales_Fact by currency will be selected regardless of date. I don't think that is the result you want. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[gaperry]

Terry,

I finally found the time and figured this one out. Thanks for your help on this.

George