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