I am having to pull data from two tables from an import process to populate a destination table. In doing so, I also need data from the current and 'prior' record. Here is the layout:
'0123', 'A', '12/10/2007'
'0123', 'B', '11/10/2010'
'0123', 'C', '10/04/2009'
For the destination table, I need to load:
'0123', 'C', '12/10/2007'
'0123', 'B', '10/04/2009'
'0123', 'A', '11/10/2010'
Tbl1 represents the current data and tbl2 is history. History in tbl2 'begins' with the StartDate in tbl1. The history table, tbl2, shows that on 11/10/2009, the Group code changed, but the value stored for Group is what it changed from, not to, thus I always have to look ahead a row (based on date) to get the proper value of Group.
All non-RBAR suggestions welcome.