January 26, 2010 at 1:12 pm
I've data in two tables, one table has trade dates and another has fx rates. There may not be a FX rate available for every trade date. The sample table structure is like:
Table1:
Trade date:
----------
01/12/2010
01/15/2010
Table2:
FX_Date FXRte
------- ------
1/11/2010 1.40
1/15/2010 1.42
I want the result set as: (If an exact match between trade-date and Fx_DAte doesn't exists, the FX rate should be picked up for the latest available date before trade date):
Trade date: FX_Date FXRte
---------- -------- ------
01/12/2010 1/11/2010 1.40
01/15/2010 01/15/2010 1.42
Pl help.
K
January 26, 2010 at 1:21 pm
Your logic make sense. I think you should continue along that line.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 26, 2010 at 1:29 pm
What have you tried so far?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 26, 2010 at 1:36 pm
The result set I've given is HOW I WANT THE DATA TO BE. Pl help on how to write a query to achieve that result set.
January 26, 2010 at 1:38 pm
We got that, we would like to see what you have tried so far to solve your problem.
January 26, 2010 at 1:40 pm
Are you only matching on trade date? Or is there some other field?
We are happy to help, but it's not our job to do your work. If you show some attempts and explain what isn't working, we'll help.
January 26, 2010 at 1:50 pm
I think I understand what you're looking for. Let me see if I understand this correctly . . .
Suppose you have a table, t1, that contains:2010-01-15
2010-01-13
2010-01-01
2010-01-11
And you have a table, t2, that contains:2010-01-15
2010-01-01
2010-01-10
So you're looking for a join that does this:
t1 t2
2010-01-15 2010-01-15
2010-01-13 2010-01-10
2010-01-01 2010-01-01
2010-01-11 2010-01-10
Is that correct?
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
January 26, 2010 at 2:03 pm
Addicted - you got it right.
Champion - this is what I've tried,bbut it's giving only the matching record but not the other one.
select t.trd_eff_tms, fx.prc_tms, fx.fxrte from fxrate fx left outer join transrptccy t on (t.trd_eff_tms = fx.prc_tms)
where t.trd_eff_tms >= (select max(prc_tms) from fxrate fx1 where fx1.prc_tms <= t.trd_eff_tms)
January 26, 2010 at 2:12 pm
friends, Pl ignore my previous query. this is what I've tried and the result set:
select t.trd_eff_tms, fx.prc_tms, fx.fxrte from transrptccy t left outer join fxrate fx on (t.trd_eff_tms = fx.prc_tms)
-- left outer join fxrate fx on (t.trd_eff_tms = prc_tms)
where t.trd_eff_tms >= (select max(prc_tms) from fxrate fx1 where fx1.prc_tms <= t.trd_eff_tms)
result set:
trd_eff_tms prc_tmsfxrte
2010-01-12 00:00:00.000NULLNULL
2010-01-15 00:00:00.0002010-01-15 00:00:00.0001.420
Desired result set:
January 26, 2010 at 2:13 pm
Now your requirement has changed slightly. For you to get better help from the people here, you will have to provide the table structure, Sample data , what is the required output and what other business rule you have.
Without that it will be difficult to give you a good advice.
-Roy
January 26, 2010 at 2:31 pm
Okay -- for anyone trying to work on this, I've put together a quick 'n dirty test scenario.
Here's the sample data:create table #test1 (tdate date)
go
create table #test2 (fdate date)
go
insert into #test1 (tdate) values ('1/15/2010')
insert into #test1 (tdate) values ('1/13/2010')
insert into #test1 (tdate) values ('1/1/2010')
insert into #test1 (tdate) values ('1/11/2010')
insert into #test2 (fdate) values ('1/15/2010')
insert into #test2 (fdate) values ('1/1/2010')
insert into #test2 (fdate) values ('1/10/2010')
As I clarified earlier, the goal is to create a join that does this:
tdatefdate
2010-01-152010-01-15
2010-01-012010-01-01
2010-01-132010-01-10
2010-01-112010-01-10
I'm messing around with some ideas, but so far, have come up empty. Hopefully, multiple eyes will come up with some more ideas.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
January 26, 2010 at 2:32 pm
Here's a solution, but I probably wouldn't use it against a large table without some kind of date filter:
CREATE TABLE Trade (trade_date DATETIME);
CREATE TABLE fx
(
fx_date DATETIME,
rate float
);
INSERT INTO trade
SELECT
'01/12/2010'
UNION ALL
SELECT
'01/15/2010';
INSERT INTO fx
SELECT
'1/11/2010',
1.40
UNION ALL
SELECT
'1/15/2010',
1.42 ;
;WITH cteGetMostRecentRate
AS (
SELECT
T.trade_date,
F.fx_date,
F.rate,
ROW_NUMBER() OVER (PARTITION BY T.trade_date ORDER BY DATEDIFF(DAY,f.fx_date,T.trade_date)) AS row_id
FROM
Trade T JOIN
fx F
ON T.trade_date >= F.fx_date
)
SELECT
*
FROM
cteGetMostRecentRate
WHERE
row_id = 1;
DROP TABLE trade;
DROP TABLE fx;
Also note how I provided sample data in an easily consumable format. You did a good job of giving desired results, but it's a lot easier to get to the results if we have the data in a consumable format along with any code you have already tried.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 26, 2010 at 2:32 pm
Grasshopper,
Does the following sample code help you?
declare @table1 table (
TradeDate datetime
);
declare @table2 table(
FXDate datetime,
FXRate decimal(6,2)
);
insert into @table1
select '20100112' union all
select '20100115';
insert into @table2
select '20100111', 1.40 union all
select '20100115', 1.42;
with MatchedDates as (
select
t1.TradeDate
,(select max(t2o.FXDate) from @table2 t2o where t2o.FXDate <= t1.TradeDate) MatchDate
from
@table1 t1
)
select
t1.TradeDate,
t2.FXDate,
t2.FXRate
from
MatchedDates md
inner join @table1 t1
on (md.TradeDate = t1.TradeDate)
inner join @table2 t2
on (md.MatchDate = t2.FXDate)
;
There is probably a better way to accomplish this, but with limited resourses (time) this is what I came up with relatively quickly.
January 26, 2010 at 2:36 pm
The force is strong today between us, Jack. 😉
January 26, 2010 at 2:46 pm
Lynn,
Yeah, I also did one with a CTE that used MAX(fx_date) instead of the Row_Number function, but, on the limited 2 rows of data the windowing function solution produced a better execution plan. Of course with no indexes that could change. And as I said, I'd be concerned if the application didn't provide some kind of date range like, only go back 7 days, because otherwise you are scanning ALL fx rows in the past.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply