currency exchange problem - 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

currency exchange problem

Author

Topic

man889
Starting Member

25 Posts

Posted - 2011-04-14 : 23:03:03

I have an invoice table and a currency rate table as following. I need to create a report convert the currency to USD or other currency. It is easy for me to convert it to USD, because the rate is base on USD in currency table. If I want to change to other currency, can I do that by SQL only without programming?

Invoice table - sample data
invId, invDate, currencyName, price
001, 1/1/2010, EUR, 10
002, 1/1/2010, HKD, 100
003, 1/1/2010, USD, 100
004, 2/1/2010, EUR, 20
005, 2/1/2010, HKD, 200
006, 2/1/2010, USD, 200

Currency table - sample data
curDate, currencyName, rate_1_usd, rate
1/1/2010, EUR, 0.7018, 1.424907
1/1/2010, HKD, 7.7545, 0.128957
1/1/2010, USD, 1, 1
2/1/2010, EUR, 0.7118, 1.434907
2/1/2010, HKD, 7.7145, 0.138957
2/1/2010, USD, 1, 1

SELECT invoice.invId, invoice.invDate, invoice.curName, invoice.price,
[currency].currencyName, [currency].rate,
invoice.price * [currency].rate AS price_USD
FROM (invoice INNER JOIN
[currency] ON invoice.invDate = [currency].valid_date AND
invoice.curName = [currency].currencyName)
ORDER BY invoice.invId

Output
1 1/1/2010 12:00:00 AM EUR 10 EUR 1.424907 14.249070
2 1/1/2010 12:00:00 AM HKD 50 HKD 0.128957 6.447850
3 1/1/2010 12:00:00 AM USD 100 USD 1.000000 100.000000
4 2/1/2010 12:00:00 AM EUR 10000 EUR 1.524907 15249.070000
5 2/1/2010 12:00:00 AM HKD 50000 HKD 0.158957 7947.850000
6 2/1/2010 12:00:00 AM USD 80000 USD 1.000000 80000.000000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-14 : 23:17:51

1st convert to your base and then convert to that curency.

Example :


SELECT 	invoice.invId, invoice.invDate, invoice.curName, invoice.price,
	[currency].currencyName, [currency].rate,
	invoice.price * [currency].rate 		AS price_USD,
	invoice.price * [currency].rate / euro.rate 	AS price_EUR
FROM 	invoice 
	INNER JOIN [currency] 	ON 	invoice.invDate = [currency].valid_date
				AND	invoice.curName = [currency].currencyName
	INNER JOIN [currency] euro	ON 	invoice.invDate = [euro].valid_date
					AND	[euro].currencyName	= 'EUR'
ORDER BY invoice.invId

KH
[spoiler]Time is always against us[/spoiler]

man889
Starting Member

25 Posts

Posted - 2011-04-14 : 23:32:57

quote:

Originally posted by khtan

1st convert to your base and then convert to that curency.

Example :


SELECT 	invoice.invId, invoice.invDate, invoice.curName, invoice.price,
	[currency].currencyName, [currency].rate,
	invoice.price * [currency].rate 		AS price_USD,
	invoice.price * [currency].rate / euro.rate 	AS price_EUR
FROM 	invoice 
	INNER JOIN [currency] 	ON 	invoice.invDate = [currency].valid_date
				AND	invoice.curName = [currency].currencyName
	INNER JOIN [currency] euro	ON 	invoice.invDate = [euro].valid_date
					AND	[euro].currencyName	= 'EUR'
ORDER BY invoice.invId

KH
[spoiler]Time is always against us[/spoiler]

Hi khtan,

Thank very much for your reply..
Can you explain this part? I don't understand it and run sql successfully.

INNER JOIN [currency] euro ON invoice.invDate = [euro].valid_date
AND [euro].currencyName = 'EUR'

Thank you very much

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-14 : 23:36:36

that is the example if you want to convert to EUR.
If you want to convert to another other currency just change the part in red to the required currency


AND [euro].currencyName = 'EUR'

KH
[spoiler]Time is always against us[/spoiler]

man889
Starting Member

25 Posts

Posted - 2011-04-14 : 23:49:23

when i try to run it,

it show that "??????"

in English means "expression not support"

I run the sql the Visual Studio Query Builder, and access 2000

Thank very much

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-14 : 23:52:48

Firstly, you have posting in a SQL Server forum. There is a separate Access forum on this site.

Second, i am not familiar with Access. Does Access support table alias ?

KH
[spoiler]Time is always against us[/spoiler]

man889
Starting Member

25 Posts

Posted - 2011-04-14 : 23:55:23

Hi khtan,

INNER JOIN [currency] euro ON invoice.invDate = [euro].valid_date
AND [euro].currencyName = 'EUR'

I don't understand euro part?
Is it like a temp view?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-14 : 23:56:23

that is the table alias

The query that i posted is meant for SQL Server. As mention earlier, I am not familiar with Access so not sure how will Access take it.

KH
[spoiler]Time is always against us[/spoiler]

man889
Starting Member

25 Posts

Posted - 2011-04-15 : 00:01:35

Hi khtan,

I will check the table alias.
I think it is what I looking for.

Thank you very much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-15 : 00:04:55

maybe you will need the AS keyword. For SQL Server this is optional

Try


INNER JOIN [currency] AS euro ON invoice.invDate = [euro].valid_date
AND [euro].currencyName = 'EUR'

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-15 : 05:24:10

use ANSI JOIN. It is easier to read and debug


FROM 	unacom1
	inner join unafin1		on	unafin1.pol_num 	= unacom1.pol_num
					and	unafin1.pol_uw_yr 	= unacom1.pol_uw_yr
					and	unafin1.endt_num 	= unacom1.endt_num
	inner join rvfcur		on	unafin1.prem_cur 	= rvfcur.currency
					and	datepart(month, unacom1.close_date) = CONVERT(int, rvfcur.valid_mth)
					abd	datepart(year, unacom1.close_date) = CONVERT(int, rvfcur.valid_year) 
	inner join rvfcur as euro 	on	datepart(month, unacom1.close_date) = CONVERT(int, euro.valid_mth)
					and	datepart(year, unacom1.close_date) = CONVERT(int, euro.valid_year) 
					and	euro.currency = 'EUR'
WHERE	unacom1.close_date between '01JAN2010' and '02JAN2010'

KH
[spoiler]Time is always against us[/spoiler]

man889
Starting Member

25 Posts

Posted - 2011-04-15 : 05:48:09

Hi khtan,

You solved it.
You are expert.

Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources