May 19, 2011 at 2:01 pm
Backend Database is SQL Server 2005.
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).
For a fromc of AED and toc of CHF, I might have the following values in my table
ex_date fromc toc rate_mul rate_div
20070101 AED CHF 0.33 1.01
20070101 EUR CHF 0.44 2.01
20070201 AED CHF 0.55 3.01
20070201 EUR CHF 0.66 4.01
20070301 AED CHF 0.77 5.01
20070301 EUR CHF 0.88 6.01
So the twelve monthly rates for 2007 would have ex_date values of 20070101, 20070201, 20070301 up to 20071201.
Note that there could be up to 65 entries where toc is CHF for a specific ex_date (eg 20070101) rather than just 2 as shown in the example data above (each entry will have a different fromc value).
Not sure what terminology I need to use to describe what I want, but taking 2007 Rates as an example, I need to do the following :
take all 12 monthly values of rate_mul and rate_div for fromc 'AED' and toc of CHF
and output them into a single row alongside the fromc of 'AED' and toc of CHF;
take all 12 monthly values of rate_mul and rate_div for fromc 'EUR' and toc of CHF
and output them into a single row alongside the fromc of 'EUR' and toc of CHF;
and so on ....
Given the sample data above , the format I need to have the output in is so that I see :
fromc toc [rate_mul_20070101] [rate_div_20070101] [rate_mul for 20070201] [rate_div for 20070201]
AED CHF 0.33 1.01 0.55 3.01
EUR CHF 0.44 2.01 0.66 4.01
Hope I've explained this clearly enough for someone to come up with a solution. I'm sure I'm
missing an obvious way to go about this, but I'm still nowhere near to a working solution.
May 19, 2011 at 2:58 pm
Look at the following two articles on cross tabs and pivot:
http://www.sqlservercentral.com/articles/63681/
http://www.sqlservercentral.com/articles/65048/
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 19, 2011 at 3:15 pm
raymurphy (5/19/2011)
Hope I've explained this clearly enough for someone to come up with a solution. I'm sure I'mmissing an obvious way to go about this, but I'm still nowhere near to a working solution.
It's enough information, but it's not an obvious solution. Primarily because you're doing what SQL Server considers a formatting and reporting function at the data layer.
If you can present the data in a way that's easily consumable, as you'll find in the first link of my signature, that'll help us present you with a result set. Basically we prefer easily runnable code to setup the scenario, and more people will get involved in finding a tested solution that way.
As Drew mentioned above, you want to look into what's called a cross-tab or a pivot. In this case, you're going to probably need dynamic SQL because of the nature of what you're trying to work with, this doesn't seem like it's going to be a static call in any way.
I'd like to flip the question on you for a minute, though. What are you trying to accomplish? Not just with the data, but what's the final result you're trying to achieve to an end user? I highly doubt they're going to be in SQL Server. If this is going to an ASP page or SSRS, you will most likely be better off performing this task at that level.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 20, 2011 at 2:09 am
Thanks for the comments, Drew and Craig ...
The user needs this data being presented in an Excel spreadsheet.
I think I'll investigate the crosstab and pivot links, so thanks for the heads up on those links and points noted regarding performing this sort of formatting and reporting functionality outside the data layer.
Thanks again ...
May 20, 2011 at 8:19 am
SSRS will export to Excel. You can also schedule reports to run automatically, so you might want to investigate using SSRS.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply