SQL 2008 – Convert Currency in run time

declare @vwINVENTORY table
(
     ProductID int,
     CurrencyCode varchar(3),
     UNIT_COST float(2),
     USD_CONVERSION_RATE float(2),
     GBP_CONVERSION_RATE float(2)
)
insert into @vwINVENTORY
     select 1, 'USD', 2, 1, 1.5 union all
     select 2, 'GBP', 40, 0.80, 1 union all
     select 3, 'USD', 67, 1, 1.51 union all
     select 4, 'GBP', 78, .81, 1 union all
     select 5, 'USD', 59, 1, 1.53 

select * from @vwINVENTORY

DECLARE @CurrencyVal char(3)
SET @CurrencyVal = 'USD'

SELECT 
CurrencyCode
, UNIT_COST
, CASE @CurrencyVal            
        WHEN 'GBP' THEN 
                        CASE CurrencyCode
                            WHEN 'GBP' THEN UNIT_COST
                            ELSE UNIT_COST * GBP_CONVERSION_RATE
                        END
        ELSE 
            CASE CurrencyCode
                            WHEN 'USD' THEN UNIT_COST
                            ELSE UNIT_COST * USD_CONVERSION_RATE
            END                
  END AS 'Converted UNIT_COST' 
, USD_CONVERSION_RATE
, GBP_CONVERSION_RATE
FROM @vwINVENTORY
Advertisements
By simplemsexchange Posted in SQL 2008

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s