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

OK

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

Code:

---

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.

Code:

---

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.:)

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.

Code:

---

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

---

It works - thanks a lot :)