July 14, 2016 at 4:35 am
hi all! first time poster here.
i have two tables 1)transactions (T1) & 2) exchange rates(T2). T1 holds daily transaction in several currencies, T2 holds daily exchange rates for all currencies.
firstly, i want to calculate the average rate for every currency for a given period (say for USD between 1 Jan 2016 to 30 Jun 2016).
i then want to bring out the transactions and converted currency amount by the calculated average rate so that the USD transaction uses the calculated USD AV. rate and gives me GBP av amount and the EURO use the EURO av. rate to convert and so on, for every single line.
the SQL to get the average rate out is as below;
select currency,avg(b.exch_rate) as avg_rate
from uviexchrates b
where date_from >'2015-01-01' and date_from < '2015-12-31'
and b.rates_to='gbp' and b.client like 'gc' group by b.currency
the above gives me something like ;
currency avg_rate
AUD 2.04
CAD 1.96
CHF 1.47
USD 1.41
my query for the Transaction table is;
select currency,cur_amount from agltransact
where period between '201600' and '201606'
the result i am after is;
cur_amount currency Av_rate converted_amount
-357000.00 EUR 1.12 -318153.46
6.55 EUR 1.12 5.84
6.55 EUR 1.12 5.84
27.77 USD 1.41 19.68
7.86 AUD 2.04 3.86
27.09 USD 1.41 19.20
54.98 CAD 1.96 28.11
the 2 far right columns are calculated. Av_rate from above 1st query & the converted_amount is the result of cur_amount * av_rate.
Question; how do i combined the 2 queries so that the above result is produced?
hope that is clear.
many thanks
July 14, 2016 at 4:48 am
WITH Rates AS (
SELECT
currency
, AVG(b.exch_rate) AS avg_rate
FROM uviexchrates b
WHERE date_from >'2015-01-01' AND date_from < '2015-12-31'
AND b.rates_to='gbp'
AND b.client LIKE 'gc'
GROUP BY b.currency
)
SELECT
t.cur_amount
, t.currency
, r.avg_rate
, t.cur_amount / t.avg_rate
FROM Rates r
JOIN Transaction t ON t.currency = r.currency
John
July 14, 2016 at 5:32 am
gosh! that was a quick reply.
and thanks that is exactly what i was after. i was not sure how to join the two queries together but as you point out using the WITH clause works.
thanks again.
July 14, 2016 at 6:47 am
The WITH isn't what joins the table together. The WITH is a CTE that creates a view of the data. In this case, it gets the averages you're looking for. That CTE is then joined to your Transaction table on the currency column.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply