Currencies - 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

General SQL Server Forums

New to SQL Server Programming

Currencies

Author

Topic

sureal808
Starting Member

3 Posts

Posted - 2008-10-28 : 14:13:20

hi guys. i am updating many currencies in a field. i would like to know how i could update many different types of currencies in the same field.

here is what i would like to do:

UPDATE Purchases
SET UnitCost = (UnitCost) * (0.274557)
WHERE CURRENCY_CODE = "QAR"
SET UnitCost = (UnitCost) * (0.0104095)
WHERE CURRENCY_CODE = "JPY"
SET UnitCost = (UnitCost) * (1.56038)
WHERE CURRENCY_CODE = "GBP"
SET UnitCost = (UnitCost) * (1.24950)
WHERE CURRENCY_CODE = "EUR";

can anyone help?

thanks

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-28 : 14:33:43

Use a central currency table, with the different exchange rates.

sureal808
Starting Member

3 Posts

Posted - 2008-10-28 : 14:38:38

that doesnt help me at all

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 14:39:54

Create a currency table then insert all the currency conversions.


create table Currency (
CURRENCY_CODE varchar(3), TO_DOLLAR float)

UPDATE PURCHASE
SET UNITCOST = (UNITCOST)*(TO_DOLLAR)
from Purchase p, Currency c
where p.CURRENCY_CODE = C.CURRENCY_CODE

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 14:42:22

quote:
Originally posted by sureal808

that doesnt help me at all

Using a centralized currency table will help you in a long run.
Because currency exchange changes every seconds, you probably want to make updates at least every day.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-28 : 14:50:56

quote:
Originally posted by sureal808

that doesnt help me at all

I worked with an ERP once, and that was the way it was implement. We also have a realtime billing system inhouse at the momenet at thats how we implemented it as well.

That way your calculations are centralized to a local table

sureal808
Starting Member

3 Posts

Posted - 2008-10-28 : 14:53:08

thats giving me a syntax error

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 15:00:58

quote:
Originally posted by sureal808

thats giving me a syntax error

Please specify the syntax error.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-29 : 04:07:08

hanbingl's code should be

UPDATE p
SET UNITCOST = (UNITCOST)*(TO_DOLLAR)
from Purchase p, Currency c
where p.CURRENCY_CODE = C.CURRENCY_CODE

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 05:17:06

quote:
Originally posted by madhivanan

hanbingl's code should be

UPDATE p
SET UNITCOST = (UNITCOST)*(TO_DOLLAR)
from Purchase p, Currency c
where p.CURRENCY_CODE = C.CURRENCY_CODE

Madhivanan

Failing to plan is Planning to fail

Surely hanbingl's should have been...


UPDATE p
SET
	UNITCOST = (UNITCOST)*(TO_DOLLAR)
from
	Purchase p
	JOIN Currency c ON c.[CURRENCY_CODE] = p.[CURRENCY_CODE]

or am I missing something? I though that ANSI joins are the only relation supported in forthcoming versions of sql server?

-------------
Charlie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-29 : 06:20:45

quote:
Originally posted by Transact Charlie

quote:
Originally posted by madhivanan

hanbingl's code should be

UPDATE p
SET UNITCOST = (UNITCOST)*(TO_DOLLAR)
from Purchase p, Currency c
where p.CURRENCY_CODE = C.CURRENCY_CODE

Madhivanan

Failing to plan is Planning to fail

Surely hanbingl's should have been...

UPDATE p
SET
UNITCOST = (UNITCOST)*(TO_DOLLAR)
from
Purchase p
JOIN Currency c ON c.[CURRENCY_CODE] = p.[CURRENCY_CODE]

or am I missing something? I though that ANSI joins are the only relation supported in forthcoming versions of sql server?

-------------
Charlie

You are correct

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources