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_rateer_eff_datecurrency_id
8.5452987612/1/20142
1.2132668312/1/20143
2.6457847212/1/20146
1.1563938612/1/20147
7.7551314212/1/201412
11912/1/201416
14.5327101512/1/201418
0.9764902112/1/201425
2.2958273112/1/201426
0.6397178612/1/201427
12.0000004812/1/201428
1.3159951912/1/201429
0.8121210812/1/201431
32.8863962512/1/201447
2350.37346812/1/201448
6.1913719612/1/201449
612.216161312/1/201450
3.4793621612/1/201466
62.772511612/1/201470
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 NameProduct SegmentProduct TypeAccount IDAccount NameCountry of BondRolling 12 Month ExposureRolling 12 Month Exposure AmountRolling 12 Month Gross Written PremiumRolling 12 Month Assumed PremiumRolling 12 Month Total PremiumRolling 12 Month Total Commission
AtlantaProduct 1Type 1111111ACCOUNT 1Canada$43,841,971$0$0$27,372$27,372$0
AtlantaProduct 1Type 1111111ACCOUNT 1United States$126,026,589$468,852$103,325$0$103,325$31,045
AtlantaProduct 1Type 1222222ACCOUNT 2Argentina$2,430,000$0$0$12,002$12,002$2,531
AtlantaProduct 1Type 1333333ACCOUNT 2United States$2,214,242$0$22,142$0$22,142$8,857
AtlantaProduct 1Type 1444444ACCOUNT 3United States$13,217,324$0$16,182$0$16,182$3,364
AtlantaProduct 1Type 1555555ACCOUNT 4United States$1,013,457$0$2,800$0$2,800$840
AtlantaProduct 1Type 2111111ACCOUNT 1United States$59,043,142$0-$7,476$0-$7,476-$1,121
AtlantaProduct 1Type 2666666ACCOUNT 5United States$13,895,346$0$2,571$0$2,571$288
AtlantaProduct 2Type 1777777ACCOUNT 6Canada$275,400$0$0$2,754$2,754$0
AtlantaProduct 2Type 1888888ACCOUNT 6United States$22,500$0$100$0$100$30
AtlantaProduct 2Type 1999999ACCOUNT 7United States$125,000$0$2,875$0$2,875$863
AtlantaProduct 2Type 1111112ACCOUNT 8United States$215,632,647$16,618,162$158$0$158$0
AtlantaProduct 2Type 1111113ACCOUNT 9United States$200,000$0$0$0$0$0
AtlantaProduct 2Type 1111114ACCOUNT 10United States$10,360,226$4,293,041$30,690$0$30,690$6,138
AtlantaProduct 2Type 1111115ACCOUNT 11United States$24,168,939$0$86,244$0$86,244$25,881
AtlantaProduct 2Type 1111116ACCOUNT 12United 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