back dated Dimension table load

[sri75]

HI ,

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

[ArndW]

There are two basic approaches to this - either add a trigger and call a stored procedure which uses SQL to update the previous and next records in the table, or to write the same logic in DataStage.
If you choose DataStage to do this you once again have a couple of possible ways to do this. One method would be to write a DataStage procedure which calls up the database reads/writes and performs the updates; another would be to have a lookup for the previous which then performs an update (if necessary) and then the same for the next.
There are other means of doing this but, unfortunately, there is no simple and easy solution to this problem.

[sri75]

Thanks Arndw For the solultion.I am working on this probelm right now

Thanks