Effective dated query
-
Hi all,
I have a base set of data that I need to join to a bunch of other tables to pull out joined information (building a data mart). This main tables holds financial data that needs to be converted into different currencies. The currency table is effective dated and I need to pull out the row that has the highest effective date that is less than my transaction date. The currency value is actually coming from one of the joined tables. So to keep it simple, I have three tables t1 which is base data (id, val1, val2, val3, trans_dt); t2 which is lookup data (id, cur_cd); t3 which is currency table (from_cur, to_cur, rate, effdt). Ordinarily I might just do a derived table from the 3 tables but the key in t1 is actually 7 fields and grouping by amount fields just doesn't sit too well with me. It seems to me I should be able to do this without a derived or temp table (like a having clause) but my brain can't seem to wrap itself around this or at least not without grouping by 27 fields. This is something I am running into more and more as financial data is often effective dated.
Any help would be appreciated.
-
I don't think there's a really nice solution to this one. But this might work:
select t1..., t2..., t3...
from
t1 inner join t2 on t1.id = t2.id cross join t3
where
t3.from_cur = t2.cur_cd and
t3.effdt = (
select max(t3s.effdt) from t3 t3s where t3s.effdt < t1.trans_dt
and t3s.from_cur = t2.cur_cd
)
This assumes that (from_cur, effdt) is a candidate key for t3.
It won't run quickly
Regards.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply