troubleshooting Question

Summarising Historical Data

Avatar of raymurphy
raymurphy asked on
Microsoft SQL Server
22 Comments1 Solution304 ViewsLast Modified:
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.


Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 22 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 22 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros