How to create view to calculate currencies?

I have the following tables:

Products
  product, idcurrency, value
   Prod1     1          5000
   Prod2     2          3000
   Prod3     3          2000

Currrency
  idcurrency, currencyID
     1          EUR
     2          USD
     3          DKK

 ForexExchan开发者_如何转开发ge

   idforexExchange   providername, idbaseCurrency, isActive
      1                   XE           Eur            1
      2                  provider2     DKK            1

ForexRates
 idForexRates  idforexExchange, toCurrency, exchangeRate
  1                1               AED        2.3
  2                1               EGY        1.3
  3                1               GBP        2.4 
  4                2               AED        7.3
  5                3               EGY        6.4
  6                4               GBP        3.4 

I want to get all the products in USD dollar for the active currency.

-all the currency filed related to the lookup table Currrency -we have forexexchange table so if we have multiple providers for the exchange rates we get the currencies rates for every exchange according to the base currency

-the forexrate table save the rates for every exchange

-we can set the active exchange from isActive so this exchange rates table will be used in the system

Expected Results 
product  Value ValueUSD
prod1    5000    25000
...............

---

Here we go:

-- sample data, for somebody else start
CREATE TABLE #Product
    ( Name varchar(50), IdCurrency int, Value decimal(15,2) )
CREATE TABLE #Currency
    ( IdCurrency int, CurrencyID char(3) )
CREATE TABLE #ForexExchange
    ( IdForexExchange int, IdBaseCurrency char(3), IsActive bit)
CREATE TABLE #ForexRates
    ( IdForexRates int, IdForexExchange int,
      ToCurrency char(3), ExchangeRate decimal(15,2) )
GO

INSERT INTO #Product VALUES ('Prod1', 1, 5000)
INSERT INTO #Product VALUES ('Prod2', 2, 3000)
INSERT INTO #Product VALUES ('Prod3', 3, 2000)
INSERT INTO #Currency VALUES (1, 'EUR')
INSERT INTO #Currency VALUES (2, 'USD')
INSERT INTO #Currency VALUES (3, 'DKK')
INSERT INTO #ForexExchange VALUES (1, 'EUR', 1)
INSERT INTO #ForexExchange VALUES (2, 'DKK', 1)
INSERT INTO #ForexRates VALUES (1, 1, 'AED', 2.3)
INSERT INTO #ForexRates VALUES (2, 1, 'EGY', 1.3)
INSERT INTO #ForexRates VALUES (3, 1, 'GBP', 2.4)
INSERT INTO #ForexRates VALUES (4, 2, 'AED', 7.3)
INSERT INTO #ForexRates VALUES (5, 3, 'EGY', 6.4)
INSERT INTO #ForexRates VALUES (6, 4, 'GBP', 3.4)
GO

And your view statement:

SELECT
    #Product.Name,
    #Currency.CurrencyID as FromCurrency,
    #ForexRates.ToCurrency,
    #ForexRates.ExchangeRate,
    #Product.Value as OriginalValue, 
    #Product.Value * #ForexRates.ExchangeRate as CalculatedValue
FROM
    #Product LEFT JOIN
    #Currency ON #Product.IdCurrency = #Currency.IdCurrency LEFT JOIN
    #ForexExchange ON
        IsActive = 1 AND
        #Currency.CurrencyID = #ForexExchange.IdBaseCurrency LEFT JOIN
    #ForexRates ON #ForexExchange.IdForexExchange = #ForexRates.IdForexExchange
--WHERE
--  #ForexRates.ToCurrency = 'USD'

I kept WHERE clause commented as you don't have any USD currency to show up. You shouldn't include that filter into your query, as it can be reused to display that product values in any currency: