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:
精彩评论