October 26, 2007 at 5:03 am
Hi,
Lets get this too.....
In AdventureWorks there are two Tables- Sales.CurrencyRate, Sales.Currency
Logic::
I need to use them to create a stored procedure that would accept 3 Input and give 1 Output
Inputs are : 1. Currency from (Currency Code)
2. Amount
3. Currency to ( Currency Code)
The output would be ConvertedValue
Simplification::
If I pass to the stored procedure GBP, 100, USD --- it should convert the 100 GBP to USD and print it through ConvertedValue Output variable.
Any help would be really appreciated .
Thanks
October 26, 2007 at 5:55 am
Was this your homework? If so, you are likely wasting your money.
without error handling:
CREATE PROC conv
@fromCurrency CHAR(3)
, @amount MONEY
, @toCurrency CHAR(3)
, @result MONEY OUTPUT
AS
BEGIN
DECLARE @rate FLOAT
IF @fromCurrency = 'USD'
SELECT TOP 1
@rate = EndOfDayRate
FROM Sales.CurrencyRate cr1
WHERE cr1.FromCurrencyCode = 'USD'
AND cr1.ToCurrencyCode = @toCurrency
ORDER BY cr1.CurrencyRateDate DESC
ELSE
SELECT TOP 1
@rate = 1 / EndOfDayRate
FROM Sales.CurrencyRate cr1
WHERE cr1.FromCurrencyCode = 'USD'
AND cr1.ToCurrencyCode = @fromCurrency
ORDER BY cr1.CurrencyRateDate DESC
SELECT @result = @amount * @rate
END
Regards,
Andras
October 26, 2007 at 6:11 am
I guess Andras is Right. This SP should work for you against the spec given by your employer/client.
October 26, 2007 at 6:21 am
KISS principle :
CREATE PROC spc_Currencyconvertor
@FromCurrencyCode CHAR(3),
@ToCurrencyCode CHAR(3),
@Amount MONEY ,
@CurrencyRateDate DATETIME ,
@ConvertedValue MONEY OUTPUT
AS
BEGIN
SET NOCOUNT ON
IF @CurrencyRateDate IS NULL
BEGIN
SET @CurrencyRateDate = getdate()
END
-- remove timepart from datetime
SELECT @CurrencyRateDate = DATEADD(d, 0, DATEDIFF(d, 0, @CurrencyRateDate))
SELECT @ConvertedValue = @Amount * CR.EndOfDayRate
FROM Sales.Currency CF
INNER JOIN Sales.CurrencyRate CR
ON CR.[FromCurrencyCode] = CF.[CurrencyCode]
AND CF.[CurrencyCode] = @FromCurrencyCode
AND CR.[CurrencyRateDate] = @CurrencyRateDate
INNER JOIN Sales.Currency CT
ON CR.[ToCurrencyCode] = CT.[CurrencyCode]
AND CT.[CurrencyCode] = @ToCurrencyCode
END
execute example:
DECLARE @rc int
DECLARE @FromCurrencyCode char(3)
DECLARE @ToCurrencyCode char(3)
DECLARE @Amount money
DECLARE @CurrencyRateDate datetime
DECLARE @ConvertedValue money
-- TODO: Set parameter values here.
EXECUTE @rc = [AdventureWorks].[dbo].[spc_Currencyconvertor]
@FromCurrencyCode
,@ToCurrencyCode
,@Amount
,@CurrencyRateDate
,@ConvertedValue OUTPUT
PRINT @ConvertedValue
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 26, 2007 at 11:18 am
Hi,
Got it working . Thanks for your time and ardent reply
You all rock :w00t:
Catch you'll later
October 27, 2007 at 5:45 am
Let us know if it got an A+:w00t:
HTH
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply