Exchange Rate Interval

Question

Post reply

Exchange Rate Interval

karthik M

One Orange Chip

Points: 29139
More actions
February 17, 2014 at 9:02 am

#285791

Hi All,
I have the below scenario.
create table cur_range
(
currency char(3),
statement_date datetime,
from_date datetime,
to_date datetime
)
insert into cur_range
select 'INR','06/30/2004','06/30/2003', '06/30/2004'
union all
select 'INR','12/31/1996','12/31/1995', '12/31/1996'
union all
select 'INR','06/30/2012','06/30/2011', '06/30/2012'
union all
select 'USD','07/30/2004','07/30/2003', '07/30/2004'
union all
select 'USD','12/31/1999','12/31/1998', '12/31/1999'
union all
select 'USD','06/30/2011','06/30/2010', '06/30/2011'
CREATE TABLE xchange_rate
(
currency_code char(3),
xchange_date datetime,
xchange_rate decimal(16,6)
)
1) This table contain the currency code's (for all currency code) xchange rate from 01/01/1990 to till date.
2) My requirement is to calculate the average xchange rate based on from_date and to_date.
i.e expected output is
currency statemtn_date from_date to_date avg(xchange_rate)
INR,06/30/2004,06/30/2003, 06/30/2004,
INR,12/31/1996,12/31/1995, 12/31/1996
INR,06/30/2012,06/30/2011, 06/30/2012
USD,07/30/2004,07/30/2003, 07/30/2004
USD,12/31/1999,12/31/1998, 12/31/1999
USD,06/30/2011,06/30/2010, 06/30/2011
avg(xchange_rate) =
1) for the first row, the data range will be between from_date
and to_date. i.e 06/30/2003 and 06/30/2004.
2) for the second row, the data range will be between from_date
and to_date. i.e 12/31/1995 and 12/31/1996
for the same currency, the avg value will be calculate based on the from_date
and to_date
I am looking for the solution which doesn't use CURSOR, HIDDEN RBAR, CORRELATED SUBQUERY , WHILE LOOP.
NEED PURE SET BASED SOLUTION!:)
Inputs are welcome!
karthik

Viewing 7 posts - 1 through 6 (of 6 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: 86948 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: 86948 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