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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply