January 15, 2015 at 3:07 pm
Good Evening stumped again, trying to convert local currency to USD through a temp table I created and CASE statement that uses a WHERE clause, is that possible?
Temp Table QUERY
SELECT a.exchange_rate
,a.er_eff_date
,a.currency_id
FROM Exchange_Rate a
WHERE a.er_eff_date = (SELECT MAX(b.er_eff_date) FROM Exchange_Rate b )
ORDER BY a.currency_id
OUTPUT Looks good pasted er_eff_ date in as Short Date to save space
exchange_rate er_eff_date currency_id
8.54529876 12/1/2014 2
1.21326683 12/1/2014 3
2.64578472 12/1/2014 6
1.15639386 12/1/2014 7
7.75513142 12/1/2014 12
119 12/1/2014 16
14.53271015 12/1/2014 18
0.97649021 12/1/2014 25
2.29582731 12/1/2014 26
0.63971786 12/1/2014 27
12.00000048 12/1/2014 28
1.31599519 12/1/2014 29
0.81212108 12/1/2014 31
32.88639625 12/1/2014 47
2350.373468 12/1/2014 48
6.19137196 12/1/2014 49
612.2161613 12/1/2014 50
3.47936216 12/1/2014 66
62.7725116 12/1/2014 70
Final Query
/****** This section returns current exchange rates for non USD currency_id *******/
IF OBJECT_ID('tempdb..##CurrentExchangeRates') IS NOT NULL
DROP TABLE ##CurrentExchangeRates;
SELECT a.exchange_rate
,a.er_eff_date
,a.currency_id
INTO ##CurrentExchangeRates
FROM Exchange_Rate a
WHERE a.er_eff_date = (SELECT MAX(b.er_eff_date) FROM Exchange_Rate b )
/****** This section returns aggregates and rolling 12 month USD Premium *******/
SELECT
d.office_name AS 'Office Name'
,b.value_desc AS 'Product Segment'
,k.value_desc AS 'Product Type'
,a.account_id AS 'Account ID'
,RTRIM(l.account_name) AS 'Account Name'
,RTRIM(c.country_name) AS 'Country of Bond'
,CASE(a.bond_amt)
WHEN a.transaction_currency_id = 1 THEN SUM(a.bond_amt)
WHEN a.transaction_currency_id != 1 THEN SUM(a.bond_amt)*m.exchange_rate WHERE a.transaction_currency_id = m.currency_id
ELSE 'Unknown Currency'
END AS 'Rolling 12 Month Exposure'
,SUM(a.gross_written_premium_amount) AS 'Rolling 12 Month Gross Written Premium'
,SUM(a.assumed_premium_amount) AS 'Rolling 12 Month Assumed Premium'
,SUM(a.total_premium) AS 'Rolling 12 Month Total Premium'
,Sum(a.total_commission) AS 'Rolling 12 Month Total Commission'
FROM Bond_Transaction_History a
LEFT JOIN lookup b
ON a.lu_business_segment = b.column_value and b.column_name = 'lu_business_segment'
LEFT JOIN Country c
ON a.country_id = c.country_id
INNER JOIN Office d
ON a.office_id = d.office_id
LEFT JOIN Month_End_Close f
ON a.month_end_close_id =f.month_end_close_id
LEFT JOIN lookup k
ON a.lu_line_of_business_type = k.column_value and k.column_name = 'lu_line_of_business_type'
INNER JOIN account l
ON a.account_id = l.account_id
INNER JOIN ##CurrentExchangeRates m
ON a.transaction_currency_id = m.currency_id
WHERE a.lu_business_segment in ('6','7') AND a.lu_method_acquisition != 2 AND DateDiff(dd,f.month_end_close_date,GETDATE()) <= 365
GROUP BY a.account_ID
,l.account_name
,b.value_desc
,k.value_desc
,d.office_name
,c.country_name
ORDER BY d.office_name
,b.value_desc
,k.value_desc
Output this is pre temp table and case statement sample, hopefully you can see that I am trying to convert $ values where Canada and Argentina are listed to USD, this is a partial output, there are multiple countries in the full data output
Office Name Product Segment Product Type Account ID Account Name Country of Bond Rolling 12 Month Exposure Rolling 12 Month Exposure Amount Rolling 12 Month Gross Written Premium Rolling 12 Month Assumed Premium Rolling 12 Month Total Premium Rolling 12 Month Total Commission
Atlanta Product 1 Type 1 111111 ACCOUNT 1 Canada $43,841,971 $0 $0 $27,372 $27,372 $0
Atlanta Product 1 Type 1 111111 ACCOUNT 1 United States $126,026,589 $468,852 $103,325 $0 $103,325 $31,045
Atlanta Product 1 Type 1 222222 ACCOUNT 2 Argentina $2,430,000 $0 $0 $12,002 $12,002 $2,531
Atlanta Product 1 Type 1 333333 ACCOUNT 2 United States $2,214,242 $0 $22,142 $0 $22,142 $8,857
Atlanta Product 1 Type 1 444444 ACCOUNT 3 United States $13,217,324 $0 $16,182 $0 $16,182 $3,364
Atlanta Product 1 Type 1 555555 ACCOUNT 4 United States $1,013,457 $0 $2,800 $0 $2,800 $840
Atlanta Product 1 Type 2 111111 ACCOUNT 1 United States $59,043,142 $0 -$7,476 $0 -$7,476 -$1,121
Atlanta Product 1 Type 2 666666 ACCOUNT 5 United States $13,895,346 $0 $2,571 $0 $2,571 $288
Atlanta Product 2 Type 1 777777 ACCOUNT 6 Canada $275,400 $0 $0 $2,754 $2,754 $0
Atlanta Product 2 Type 1 888888 ACCOUNT 6 United States $22,500 $0 $100 $0 $100 $30
Atlanta Product 2 Type 1 999999 ACCOUNT 7 United States $125,000 $0 $2,875 $0 $2,875 $863
Atlanta Product 2 Type 1 111112 ACCOUNT 8 United States $215,632,647 $16,618,162 $158 $0 $158 $0
Atlanta Product 2 Type 1 111113 ACCOUNT 9 United States $200,000 $0 $0 $0 $0 $0
Atlanta Product 2 Type 1 111114 ACCOUNT 10 United States $10,360,226 $4,293,041 $30,690 $0 $30,690 $6,138
Atlanta Product 2 Type 1 111115 ACCOUNT 11 United States $24,168,939 $0 $86,244 $0 $86,244 $25,881
Atlanta Product 2 Type 1 111116 ACCOUNT 12 United States $59,900 $0 $0 $0 $0 $0
January 20, 2015 at 10:53 am
WAs able to figure this one out had a problem in the temp table subquery logic which was not returning all instances of most current exchange rate by currency type.
Here was the fix
select a.currency_id,
a.er_eff_date,
a.Exchange_Rate
from Exchange_Rate a
inner join (
select currency_id,
MAX(exchange_rate_id) as 'max_id'
from Exchange_Rate
group by currency_id) b
on a.exchange_rate_id = b.max_id
order by a.currency_id
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply