Exchange Rate Interval

Question

Post reply

Exchange Rate Interval

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Something like this?

SELECT currency, AVG( xr.xchange_rate)

FROM cur_range cr

JOIN xchange_rate xr ON cr.currency = xr.currency_code

AND xr.xchange_date BETWEEN cr.from_date AND cr.to_date

GROUP BY currency

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dwain Camps SSC Guru Points: 86978 More actions · Answer 2

Are you looking for a weighted average (by day within date range) for the average exchange rate?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

karthik M One Orange Chip Points: 29139 More actions · Answer 3

No. It is not giving the expected result set. I have already tried this.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 4

dwain.c (2/17/2014)
Are you looking for a weighted average (by day within date range) for the average exchange rate?

yes.

karthik

Dwain Camps SSC Guru Points: 86978 More actions · Answer 5

karthik M (2/17/2014)
dwain.c (2/17/2014)
Are you looking for a weighted average (by day within date range) for the average exchange rate?
yes.

I suggest then that you reduce the date range (from, to) in your cur_range table and provide some consumable sample data for the second table, along with the calculated amount (expected value) for the weighted average exchange rates.

There is nothing like doing the calculation manually to cause the light bulb to pop on as to how to construct the query.

Edit: BTW. The only reason that Luis's suggestion wouldn't work for you is if you're missing some days in the second table, meaning that an exchange rate exists for one day and carries over into the missing days. That throws off the weighting of the average.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

karthik M One Orange Chip Points: 29139 More actions · Answer 6

After including start_date & end_date in the select clause & group by clause, I got the expected result set.

karthik