February 19, 2015 at 7:32 am
CREATE A STORE PROCEDURE : CONVERT_TO_STAT_CURRENCY
Input
NET_AMOUNT
DESTINATION CURR
TARGET CURR
Output
NET_AMOUNT (Limit up to 2 decimal places)
TARGET_CURR
Logic: -
Step1: Pass DESTINATION_CURR to CURRENCY TABLE and pull the exchange rate. Convert into USD by multiplying its exchange rate found in CURRENCY TABLE . [MULTIPLICATION in step 1]
Example: 2,723.78 EUR is Net Amount. Convert this into USD which means 2,723.78 * 1.13200 = 3083.32 USD (Check the table exchange rates)
Step2: Pass TARGET_CURR to CURRENCY_TABLE and pull the exchange rate. Take the value derived in step 1 and convert into Statistical currency by dividing its exchange rate found in CURRENCY_TABLE. [DIVISION in step2]
GBP to USD exchange rate found in CURRENCY table is 1.50670
Example: To convert into Statistical rate (GBP) = 3083.32 found in step 1 / 1.50670 in USD gives value of 2046.40 GBP. This gives Target value in Statistical currency.
Test Cases:
Case-1) Input - If both Document Currency and Target Currency are same
Output - O_NET_AMOUNT = I_NET_AMOUNT
O_TARGET_CURR = I_TARGET_CURR
Case-2) Input - If the Document Currency is in 'USD' and Target Currency is other than 'USD'
Ignore Step1 and Implement the logic described only in Step2 and exit the function with derived values.
Output - O_NET_AMOUNT = <Derived value from Step2>
O_TARGET_CURR = I_TARGET_CURR
Case-3) Input - If the Document Currency is other than 'USD' and Target Currency is 'USD'
Implement the logic described only in Step1 and exit the function with derived values.
Output - O_NET_AMOUNT = <Derived value from Step1>
O_TARGET_CURR = I_TARGET_CURR
Case-4) For rest all other cases: Proceed with the Logic below starting Step1 and then Step2 to derive Statistical value.
Output - O_NET_AMOUNT = <Derived value from both Step1 and Step2>
O_TARGET_CURR = I_TARGET_CURR
Please SQL professionals, can you help me to create store procedure for above cases with two logics.
Many Thanks in Advance.
February 19, 2015 at 3:15 pm
Hmmmmm......
Now this looks incredibly like homework\assignment material...
Why don't you post us what you have tried yourself so far and we can make comment? We won't do your homework for you, but we can help you along the way if you at least have a go yourself.
February 19, 2015 at 3:27 pm
I have created a function which gives country list and exchange rate with respect to USD.
COUNTRY RATE
INR 0.019
GBR 1.45
EUR 1.6 ( MIGHT EXCHANGE RATE IS NOT CORRECT HERE FOR ALL COUNTRIES) .
could you please help with case 2 if possible.
February 19, 2015 at 3:29 pm
February 19, 2015 at 3:41 pm
I don't have access to my company Computer. Yeah the function works. I gave function name as fn_USD_currency converter.
February 19, 2015 at 4:18 pm
OK, I got to here and now it's too late to continue. You really need to provide us with at very least the schema of the tables you're using and some sample data. Check the tips in my signature for some help..
Looks like you need to select the EXCHANGE_RATE from the CURRENCY_TABLE where currency = TARGET_CURRENCY and the divide the result of step 1 by this figure.
psuedo-code:
if TARGET_CURR = DESTINATION_CUR
SELECT I_NET_AMOUNT, I_TARGET_CUR
else if DESTINATION_CURR = 'USD' AND TARGET_CURR <> 'USD'
(
SELECT NET_AMOUNT * EXCHANGE_RATE
FROM CURRENCY_TABLE
WHERE CURRENCY = DESTINATION_CURR
)
/
..................................tbc when you've provided the schema and some test data.
Night 🙂
February 19, 2015 at 6:49 pm
I don't now. There's a borderline between "help me figure out this problem I'm stuck on" and "please do my job for me" and you seem to be coming close to the second.
First, it would be nice if you gave us a properly constructed sample we can test with. That means DDL for test tables, code to insert test data and an idea what output you want.
And in this case, at the very least a description of what you have tried and why it doesn't seem to be working.
February 19, 2015 at 9:02 pm
The thing I see missing from this is that currency exchange rates change daily. Where's the effective date for lookup of the exchange rate?
Note that this doesn't need to be in an SP. It is a one-liner (even with effective date) that would best be written as an inline table valued function to give the best performance.
I've done so, hence speaking from experience.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 20, 2015 at 12:27 am
Doing double hop currency conversion is always fun, this is an example of how to do it from a system I worked on.
SELECT
FromCCY.CCYAS FromBaseCCY
, ToUSD.CCYAS ToUSDCCY
, FromCCY.CCYRateAS FromBaseToGBP
, ToUSD.CCYRateAS FROMGBPToUSD
, ToUSD.CCYRate/FromCCY.CCYRate USDRate
FROM
ExchangeRate FromCCY
CROSS APPLY
(SELECT * FROM ExchangeRate where CCY='USD') ToUSD
The data we had was from any currency to GBP, but we also then had to convert to USD in the same process.
All this does is effectively get all the GBP rates, applies the USD rate to the base rate to create a composite rate, in terms of USD to USD you end up with 1
Thankfully the rate was always at current day, so we didn't have to worry about rates on a specific date, but that's easily fixes with a join on date.
You can either put this into a CTE or Inline Table function, depending on how often its going to be used.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 20, 2015 at 12:37 am
Jason-299789 (2/20/2015)
Thankfully the rate was always at current day, so we didn't have to worry about rates on a specific date, but that's easily fixes with a join on date.
Hehe. More or less true. It becomes just a tad more challenging when rates aren't always updated daily (e.g., some might get updated once a week or only every 2-3 days if they aren't used much). Then that JOIN needs to find the closest matching date.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 20, 2015 at 12:51 am
Hi dwain,
We don't update currency exchange rate daily or monthly, it's a fixed table with standard exchange rate, our company will update the exchange table if there is massive change in price movement. At the moment we have table with listing all countries with USD currency exchange rate. THIS TABLE SATISFIES all my 4 test cases.
Example
Case 1) inputs ( SOURCE currency 'AUD', TARGET CURRENCY 'AUD' AMOUNT = 500)
THE OUTPUT WILL BE ( TARGET CURRENCY : AUD, AMOUNT 500)
CASE 2)inputs ( SOURCE currency 'USD', TARGET CURRENCY 'GBP' AMOUNT = 500)
THE OUTPUT WILL BE ( TARGET CURRENCY : AUD, AMOUNT = (500/1.506) ( exchange rate of GBP 1.506)
CASE 3)CASE 2)inputs ( SOURCE currency 'if not USD',(here may be 'gbp') TARGET CURRENCY 'USD' AMOUNT = 500)
THE OUTPUT WILL BE ( TARGET CURRENCY : USD, AMOUNT = (500×1.506) ( exchange rate of GBP 1.506)
CASE 4) inputs ( source currency = 'GBP', TARGET CURRENCY 'AUD ' AMOUNT = 500)
THEN OUTPUT AMOUNT WILL BE (( logic 1) 500×1.506) = 753), then now need to apply logic 2 (753/0.776)= 970.36
( here AUD CONVERSION RATE IS 0.776 with respect to USD)
I need to write a store procedure/function to check the inputs whether they come which case and do respect calculations to obtain output amount.
February 20, 2015 at 12:52 am
Hi dwain,
We don't update currency exchange rate daily or monthly, it's a fixed table with standard exchange rate, our company will update the exchange table if there is massive change in price movement. At the moment we have table with listing all countries with USD currency exchange rate. THIS TABLE SATISFIES all my 4 test cases.
Example
Case 1) inputs ( SOURCE currency 'AUD', TARGET CURRENCY 'AUD' AMOUNT = 500)
THE OUTPUT WILL BE ( TARGET CURRENCY : AUD, AMOUNT 500)
CASE 2)inputs ( SOURCE currency 'USD', TARGET CURRENCY 'GBP' AMOUNT = 500)
THE OUTPUT WILL BE ( TARGET CURRENCY : AUD, AMOUNT = (500/1.506) ( exchange rate of GBP 1.506)
CASE 3)CASE 2)inputs ( SOURCE currency 'if not USD',(here may be 'gbp') TARGET CURRENCY 'USD' AMOUNT = 500)
THE OUTPUT WILL BE ( TARGET CURRENCY : USD, AMOUNT = (500×1.506) ( exchange rate of GBP 1.506)
CASE 4) inputs ( source currency = 'GBP', TARGET CURRENCY 'AUD ' AMOUNT = 500)
THEN OUTPUT AMOUNT WILL BE (( logic 1) 500×1.506) = 753), then now need to apply logic 2 (753/0.776)= 970.36
( here AUD CONVERSION RATE IS 0.776 with respect to USD)
I need to write a store procedure/function to check the inputs whether they come which case and do respect calculations to obtain output amount.
February 20, 2015 at 6:14 am
Hi Jason,
Your script nearly satisfies the two cases, but I need it as function or store procedure to satisfy all the 4 cases. I am glad for your script.
February 20, 2015 at 6:21 am
I have created this function get USD exchange rate from currency exchange tabe( The table gives all countries exchange rate with respect to USA)
CREATE FUNCTION [dbo].[CurrencyRate] (@Currency AS varchar(3))
RETURNS numeric(15, 3)
AS
BEGIN
RETURN
(
SELECT RATE
FROM CURRENCY
WHERE CURRENCY = @Currency
AND RTYPE = @RType
);
END
Now I believe, I can use this function or table to create a SP/Function to satisfy all cases.
February 20, 2015 at 6:27 am
Hi Jim,
I really appreciate your gesture of helping sign. I am using script to built the required Function.
Many Thanks.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply