[Resolved] - SQL query re: Exchange rates - data included.

[RSINGH]

OK

I have a table of exchange rates as below against USD.

BaseCurrency	Traded	Rate
USD		ATS 	13.644323
USD		AUD 	1.7755681818181817
USD		BRL 	3.6749999999999998
USD		CAD 	1.5840000000000001
USD		CHF 	1.4527000000000001
USD		CLP 	704.89999999999998
USD		COP 	2705.0
USD		CZK 	30.361999999999998
USD		DEM 	1.9393456
USD		DKK 	7.3613999999999997
USD		ESP 	164.98363900000001
USD		EUR 	0.99157164105106599
USD		GBP 	0.63307166371233226
USD		USD	1

Now I know it is possible truiangulate these so that I have exchange rates across all currencies. I would like is to get all the exchange rates across Eur, USD and USD against all currencies as in the table below. Can anybody help. It can be done in SQL and I think it involves various self joins converting to USD and then to the relevant base currency but my brain has melted.

USD		ATS 	13.644323
USD		AUD 	1.7755681818181817
USD		BRL 	3.6749999999999998
USD		CAD 	1.5840000000000001
USD		CHF 	1.4527000000000001
.....
GBP		ATS 	[rate]
GBP		AUD 	[rate]
GBP		BRL 	[rate]
GBP		CAD 	[rate]
GBP		CHF 	[rate]
.....
EUR		ATS 	[rate]
EUR		AUD 	[rate]
EUR		BRL 	[rate]
EUR		CAD 	[rate]
EUR		CHF 	[rate]

where [rate] is the calculated value
All help will be greatly appreciated.

[brucevde]

Interesting problem. I haven't worked out the complete answer but here's a start. First are you inserting records or just updating the rates? I am not sure my calculation is correct. The sample is only a Select statement but once you verify the data you could add an Insert Into.

Select 
	ER2.Traded, 
	ER.Traded, 
	ER.Rate / ER2.Rate as NewRate

From
	[Exchange Rates] ER Join [Exchange Rates] ER2 On ER.BaseCurrency = ER2.BaseCurrency 

Order By ER2.Traded

[RSINGH]

It works - thanks a lot