Toad World® Forums

Adding Conversion Rates from another Table


#1

Hello Group!

I’m the newbie to SQL/TOAD as I’m not a DBA by training. I apologize my questions are probably very basic. But I am learning.

I’m trying to convert rates that are stored in US Dollars into rates that are in Canadian Dollars for properties that in Canada. I’m not sure how to do this. Here is where I believe I need to do something (but what?)

The first script outputs the below information based on a date in the future (example: 6/1/2015). The script looks something like this:

SELECT
dmart.get_full_date(a.report_date_key) report_date,
d.full_date as Stay_Date, d.day_cd, d.wkdy_ind, d.mo_name, d.yr_num,
p.geo_metro_name,
p.geo_divsn_name,
p.geo_region_name,
p.OPS_AMD_DESC SDO,
p.prop_master_id,
p.prop_name,

sum(a.otb_rm_rate_usd_amt) REV,
sum(a.OTBLY_RM_RATE_USD_AMT) REV_LY,
sum(a.OTB364_RM_RATE_USD_AMT) REV_364

To clarify,

a.OTB_RM_RATE_USD_AMT - The rate (in US Dollars) used for 6/1/2015

a.OTBLY_RM_RATE_USD_AMT - the rate (in US Dollars) that was used on 6/1/2014

a.OTB364_RM_RATE_USD_AMT - the rate (in US Dollars) that was used on the same day of the week of last year (6/2/2014)

We do store the conversion rates in a data table that goes back to January 1 of 2000. I can extract that data with the following script:

select
b.full_date,
a.cur_exchng_from_usd_rate
from
d_cur_exchng a,
crmmart.d_date_period b
where
a.date_key = b.date_key and
cur_key = 113 and
rcrd_sts_cd = ‘A’

order by
full_date

To clarify,

b.full_date - The date of the listing (begins at January 1, 2000)

a.cur_exchng_from_usd_rate - The actual exchange rate for the date listed

cur_key = 113 - We are an international company and have to keep up with multiple exchange rates. The Canadian Dollar is the 113th key.

The Question: How do I update the script to find and apply the conversion?

Can you help?

Don


#2

Group,

After much trial and error, I figured it out. Here’s what I did:

case

when p.COUNTRY_NAME in ‘CANADA’

then sum(a.otb_rm_rate_usd_amt) * (SELECT d_cur_exchng.cur_exchng_from_usd_rate

FROM d_cur_exchng,

crmmart.d_date_period

WHERE d_cur_exchng.date_key = crmmart.d_date_period.date_key and

dmart.get_full_date(a.report_date_key) = crmmart.d_date_period.full_date and

cur_key = 113 and

rcrd_sts_cd = ‘A’)

else sum(a.otb_rm_rate_usd_amt)

end as REV

Feel free to use this as example if you need to do the same thing.

Don