All Forums UDA
deagle 17 posts Joined 11/08
06 Jan 2009
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

joedsilva 505 posts Joined 07/05
06 Jan 2009

I didn't test, but you can try something like this.inner joinon version.reference = instruction.referenceand instruction.instructiondate >= version.effectivedatequalify rank() over(partition by instruction.reference order by version.effective date desc) = 1

deagle 17 posts Joined 11/08
21 Jan 2009

HiI 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

You must sign in to leave a comment.