Using the 'most recent' record for a given repeated key in a join?

[Penny]

I am working on a model in Framework Manager (v 10.2.1)  based on a relational Oracle database.  I am looking for advice on the best way to do the following:

I have two tables - the first table is comprised of list of unique combination of foreign keys TABLE_A.  I want to join this table to another table where the foreign key from the first table is repeated TABLE_B.

In TABLE_B, each time a record is modified, the original record is left intact and a new one is created.  The database uses an effective year column in the table to determine which is the most recent.  So what I want is my join to use only the record from TABLE_B with the maximum effective year. 

Can I do this in framework manager?  The only thing I can think of is to create a database function that I can call in my join which will return the maximum effective year for the given foreign key.  Then my join expression could say ....AND FunctionCall(Parameters) = TABLE_B_EFFECTIVE_YEAR....

Any feedback is greatly appreciated and if I am missing something obvious I apologize in advance.

[Penny]

Decided to use a summary filter with maximum function in report studio for the moment.  I am going to review how heavily this will be used in the future (how many reports) and if it is worth it, will build a database function.

Thanks

[Lynn]

What you describe sounds very much like table B is a slowly changing dimension. I would expect that the database design of table B would have both an effective and an expiration date. The latest unexpired record would carry a date far into the future or a null making it easily identifiable as the latest. Trying to determine it on the fly with max functions is asking for performance problems right out of the gate.

[Penny]

Yes I would agree that TABLE_B is a slowly changing dimension and thank-you.