Query Using Current and Prior Records
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:
tbl1:
VCode: VARCHAR(4)
Group: CHAR(1)
StatrtDate: DATETIME
tbl2:
VCode: VARCHAR(4)
Group: CHAR(1)
ChangeDate: DATETIME
ChangeReason: CHAR(1)
Sample data:
Tbl1:
'0123', 'A', '12/10/2007'
Tbl2:
'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.
tbl1:
VCode: VARCHAR(4)
Group: CHAR(1)
StatrtDate: DATETIME
tbl2:
VCode: VARCHAR(4)
Group: CHAR(1)
ChangeDate: DATETIME
ChangeReason: CHAR(1)
Sample data:
Tbl1:
'0123', 'A', '12/10/2007'
Tbl2:
'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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Rimvis:
This seems to work with one caveat; the supplied history table contains values of VCode where there are duplicate ChangeDates. For example, on 1/12/2010, for VCode 'AAAA' there can be two rows, one with a Group value of 'A' and one with a value of 'B'. I believe this to be an error in data entry and am going to have the client clean up this data.
Thank you for your solution.
This seems to work with one caveat; the supplied history table contains values of VCode where there are duplicate ChangeDates. For example, on 1/12/2010, for VCode 'AAAA' there can be two rows, one with a Group value of 'A' and one with a value of 'B'. I believe this to be an error in data entry and am going to have the client clean up this data.
Thank you for your solution.
ASKER
Had current and history turned around in the comments, but I figured if I couldn't figure that out then I shouldn't be a programmer :-) Thanks again.
>>Had current and history turned around in the comment
Yes, sorry about that
Yes, sorry about that
ASKER
Also, to explain better what I need, when there is history for a VCode, then, starting with the earliest record (first ChangeDate), the data that needs to go into the destination table is:
1. StartDate from tbl1 as EffectiveDate and Group code from the earliest record.
2. ChangeDate from the first (earliest) record and Group code from the next-to-the-earliest record.
3. ChangeDate from the next-to-the-earliest record and Group code from the next-to-the-next-to-the-ea
n. ChangeDate from the latest record and Group code from tbl1.