I am working on person dimension table.
I have few questions regarding the data that is there in source table.
Source table data
EMPLID EFFDT NAME
100 01/05/2010 ABC
100 01/14/2010 ABCD
100 01/12/2010 ABCE -- this is back dated row.
When I load the data into dimension table
first two rows there won't be any problem
EMPL_SID EMPLID EFFDT NAME EFF_START_DT EFF_END_DT
1 100 01/05/2010 ABC 01/05/2010 01/13/2010
2 100 01/14/2010 ABCD 01/14/2010 NULL
3 100 01/12/2010 ABCE 01/12/2010 ?
I need to update the previous records EFF_START_DT and EFF_END_DT based on 01/12/2010 (becuse this is back dated row)
my final output will be like this
EMPL_SID EMPLID EFFDT NAME EFF_START_DT EFF_END_DT
1 100 01/05/2010 ABC 01/05/2010 01/11/2010
2 100 01/14/2010 ABCD 01/14/2010 NULL
3 100 01/12/2010 ABCE 01/12/2010 01/14/2010
Can you please give me some advice on how to do this kind of change
Thanks
Code: Select all