Return a version id as at a particular date

UDA
Enthusiast

Return a version id as at a particular date

Hi guys,

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.

VERSION TABLE

|reference | ver_id | effective_dt |

|1 | 1 | 01/01/2003 |

|1 | 2 | 01/01/2005 |

|1 | 3 | 01/01/2010 |

INSTRUCTION TABLE

|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.

Hope you can help and thanks in advance
2 REPLIES
Enthusiast

Re: Return a version id as at a particular date

I didn't test, but you can try something like this.

inner join
on version.reference = instruction.reference
and instruction.instructiondate >= version.effectivedate

qualify rank() over(partition by instruction.reference order by version.effective date desc) = 1
Enthusiast

Re: Return a version id as at a particular date

Hi

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!!!!

Any thoughts