Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Exchange Rate Interval Expand / Collapse
Author
Message
Posted Monday, February 17, 2014 9:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:43 AM
Points: 2,020, Visits: 2,515
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
Post #1542182
Posted Monday, February 17, 2014 9:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:00 PM
Points: 2,763, Visits: 5,905
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1542190
Posted Monday, February 17, 2014 5:19 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1542312
Posted Monday, February 17, 2014 10:51 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:43 AM
Points: 2,020, Visits: 2,515
No. It is not giving the expected result set. I have already tried this.


karthik
Post #1542369
Posted Monday, February 17, 2014 10:52 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:43 AM
Points: 2,020, Visits: 2,515
dwain.c (2/17/2014)
Are you looking for a weighted average (by day within date range) for the average exchange rate?


yes.


karthik
Post #1542372
Posted Monday, February 17, 2014 11:26 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1542379
Posted Tuesday, February 18, 2014 3:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:43 AM
Points: 2,020, Visits: 2,515
After including start_date & end_date in the select clause & group by clause, I got the expected result set.


karthik
Post #1542448
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse