We help IT Professionals succeed at work.
Get Started

Summarising Historical Data

raymurphy asked
Last Modified: 2012-05-11
Have table named hist_exch_rates holding exchange rates info with following structure :

ex_date          char(8)
fromc              char(3)
toc                  char(3)
rate_mul          float
rate_div          float

Table holds historical exchange rate info going back to 2007 (starting with ex_date value of
20070101) and up to current date (with ex_date value of 20110601). Typical contents for Jan 2007 would be :

ex_date         fromc  toc   rate_mul  rate_div
20070101      AED    CHF   0.33        3.01
20070101      ARS    CHF   0.44        2.51

Typical contents for Feb 2007 would be :

ex_date         fromc  toc   rate_mul  rate_div
20070201      AED    CHF   0.55        3.01
20070201      ARS    CHF   0.66        2.51

I am only interested in dealing with entries where to_currency is CHF.

For each month, there can be 65 entries where the toc would be CHF (fromc would be different
as in the above data samples).

I've been asked to prepare a historical report - essentially showing all entries where
toc is CHF for the last five years (from ex_date of 20070101 through to ex_date of 20110601)
but the output needs to summarised in the following sort of format :
fromc toc      JAN07rate_mul    JAN07rate_div       FEB07rate_mul     FEB07rate_div

AED   CHF      0.33                     3.01                       0.55                      3.01
ARS   CHF      0.44                     2.51                      0.66                       2.51

So on the output the JAN07ratemul and JAN07rate_div columns would go all the way along to MAY11rate_mul and MAY11rate_div.

Database is SQL Server 2005. Hope I've explained what I need clearly, and would really
appreciate some assistance in getting the data into the required outputput format.

Watch Question
This problem has been solved!
Unlock 1 Answer and 22 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE