Currency handling..? 1

[kenjoswe]

Hi all,

How can I handle currency?

I download a currency table in SEK that look like:

Date Currency Currency_Rate
1 DKK 1,2
1 EUR 7,8


From the above table I would like to create:

Date Currency1 Currency2 Currency_Rate
1 SEK DKK 1,2
1 SEK EUR 7,8
1 DKK SEK 0,8
1 DKK EUR 6,5
1 EUR SEK 0,12
1 EUR DKK 0,15

How can this be done with SQL-syntax?

/Kent J-

 

[aalmeida]

Well how do you plan to come up with two columns for Currency? what is SEK? AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[kenjoswe]

aalmeida,

SEK=Swedish krona
DKK=Danish krona
USD=US Dollar
EUR=Euro

The first table is SEK to DKK and SEK to EUR:

Date Currency Currency_Rate
1 DKK 1,2
1 EUR 7,8

From the first table it is possible to calculate other relations as well. DKK to EUR is 7,8/1,2.
So therefore I can create a second table with SEK to EUR and DKK to EUR.

/Kent J.

 

[aalmeida]

To acomplish that the esyest way it comes to my mind would be to create a sproc that would do:
1st Select distinct Currency into NewTable
2nd run a cursor on first table to select each curency and it's curency2 ratio
3re while @@fetchstatus =0
4th calculate new ratios and update NewTable with results
5th go to next currency
6th Select * from NewTable
you are done

But that is the thing I'm not sure that is true that the currencys have a direct relation to each other to the sense that if A = 3B and C = 4B then C = 4A/3 for what I know C could be c = 2A since this value is direct calculated from the average trade of each currency on each stock market basesd on the volume of the trade of good between them.
If so would not be that the more acurate result would be to compare it to a stable curency, like the USD, and then apraise all of then based on it so your table would be:
Date Currency1 Currency2 Currency_Rate
1 SEK USD 1,2
1 DKK USD 6,5
1 EUR USD 0,12
AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[grahamm]

If you can add an entry to the source table for SEK e.g.

Date Currency Currency_Rate
1 DKK 1,2
1 EUR 7,8
1 SEK 1,0

then a non-equi join will do it:

select a.date, a.code,b.code, a.rate/b.rate
from exchange a ,exchange b
where a.code <> b.code

If you can't add a row to the source table, then create another table with one row (can be a temp table)
then use a union + non-equi join. If sek is this one row table then:

select a.d, a.code,b.code, a.rate/b.rate from
(select * from exchange union select * from sek) a ,
(select * from exchange union select * from sek) b
where a.code <> b.code