I am looking for a solution to the following problem. I have two tables as outlined below. I want to join these two tables based on the reference column but only want versions effective as at the instruction date.
|reference | ver_id | effective_dt |
|1 | 1 | 01/01/2003 |
|1 | 2 | 01/01/2005 |
|1 | 3 | 01/01/2010 |
|reference | instruction_dt |
|1 | 01/01/2008 |
In the example above, the record that I would expect to be joined with the instruction table is record 2, instruction date 01/01/2005.
I wanted to give you feedback on you solution provided. It did not give me back the latest version of the instruction when I tested it. I expect to see one version record per instruction and that record being the version active at a point in time ie effective at instruction date. I have inserted all versions into a tmp table for all values greater than the eff_date and then selected the max version, not an ideal solution and i'm sure there is a more efficient and cleaner way to do this!!!!