converting euro to usd - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

converting euro to usd

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2008-08-20 : 07:25:39

does anyone no how to write a stored procdeure that converts euro rates to usd rates.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-20 : 07:33:12

well you have to get the conversion rates from somewhere... and as they change constantly you'll probably need to call an outside webservice that provides this.
if you go this way sql server isn't a place to do this.

but if you just need a one time convert look up the current exchange rate on the web and create a simple update.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-20 : 07:33:16

This is a fairly broad question. It depends on your database tables, how the rates are updated regularly etc. Here's some basics on stored procedures you might find helpful: http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2008-08-20 : 07:43:15

ya i might not have explained myself correctly.at the moment we have a procedure. the info is pulled for the new york fed wedsite all the rates are pulled in daily and they are all usd to euro usd to aud etc. now we are pulling the new data from ecb which is euro to usd etc. i need the new feed from ecb to read like nyf.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-20 : 08:27:39

so then what's the problem?
EUR/USD = X => USD = EUR/X, EUR = X*USD

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2008-08-20 : 09:06:20

this is the code for the one we use

DECLARE @usTo smallmoney
SET @usTO = (SELECT TOP 1
CASE
WHEN c.Invert = 0
THEN ex.ExchangeRate
ELSE CONVERT(float,1 / ex.ExchangeRate)
END
AS ExchangeRate
FROM ExchangeRate ex
INNER
JOIN Currency c
ON c.CurrencyID = ex.CurrencyID
WHERE ex.RateDate < = @rateDate
AND ex.CurrencyID = @toID
ORDER BY ex.RateDate DESC )
DECLARE @usFrom smallmoney
SET @usFrom = 1
IF @fromID <> 6
BEGIN
SET @usFROM = (SELECT TOP 1
CASE
WHEN c.Invert = 1
THEN ex.ExchangeRate
ELSE CONVERT(float,1 / ex.ExchangeRate)
END
AS ExchangeRate
FROM ExchangeRate ex
INNER
JOIN Currency c
ON c.CurrencyID = ex.CurrencyID
WHERE ex.RateDate < = @rateDate
AND ex.CurrencyID = @fromID
ORDER BY ex.RateDate DESC )
END
SET @rate = @usFrom * @usTO

IF @toID = 6
BEGIN
SET @rate = (SELECT TOP 1 ExchangeRate
FROM ExchangeRate
WHERE RateDate < = @rateDate
AND CurrencyID = @fromID
ORDER BY RateDate DESC )
END

--SELECT @usFrom, @usTO, @rate

RETURN @rate
END

how does what you have there work with this

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-21 : 04:38:11

I presume the id=6 is US currency? (Ideally this should be a named variable/constant for easier maintenance.)
If this is the case, I would think that all you need to do is change the 6 to the ID for Euro & change the US variable names Euro to reflect this. You shlould check that the busines rules in the data you are receiving for inversions (invert) etc are the same. Of course you should also check examples of the results to ensure it's doing what it is supposed to.

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2008-08-21 : 05:17:52

yes the id 6 is the us currency.im taking the inversions out of the code as well so thats fine.
thanks

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2008-08-21 : 07:34:10

if im taking out the inverted part of the sql how should it look for it to work

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2008-08-21 : 09:51:32

would this work all my inverts in the table will be set to 0
USE [Harvest]
GO
/****** Object: UserDefinedFunction [dbo].[udfForex] Script Date: 08/21/2008 10:19:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION
[dbo].[udfForex]
(
@rateDate datetime ,
@fromID tinyint ,
@toID tinyint
)
RETURNS smallmoney
AS
BEGIN
--DECLARE @rateDate datetime
--DECLARE @fromID tinyint
--DECLARE @toID tinyint
--SET @rateDate = CONVERT(datetime,'5/9/2006')
--SET @fromID = 8
--SET @toID = 3
IF @fromID = @toID
RETURN 1

DECLARE @rate smallmoney

DECLARE @usTo smallmoney
SET @usTO = (SELECT TOP 1
CASE
WHEN c.Invert = 0
THEN ex.ExchangeRate
ELSE CONVERT(float,1 / ex.ExchangeRate)
END
AS ExchangeRate
FROM ExchangeRate ex
INNER
JOIN Currency c
ON c.CurrencyID = ex.CurrencyID
WHERE ex.RateDate < = @rateDate
AND ex.CurrencyID = @toID
ORDER BY ex.RateDate DESC )
DECLARE @usFrom smallmoney

IF @toID =3
BEGIN
SET @rate = (SELECT TOP 1 ExchangeRate
FROM ExchangeRate
WHERE RateDate < = @rateDate
AND CurrencyID = @fromID
ORDER BY RateDate DESC )
END

--SELECT @usFrom, @usTO, @rate

RETURN @rate
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources