Logic to update end_dt for previous records

[ajay.prakash03]

col1,col2.col3 as key columns and eff_dt in source. Source and target both are SQL server.
Need 5 field in target (col1,col2,col3,Start_dt,End_dt).
Day 1, I am geting below records:-

inPut records->day1_data__

col1 col2 col3 eff_dt
A 41172 2004-10-13 2012-02-27

In O/p-->need

OutPut--->day1
col1 col2 col3 Start_dt end_dt
A 41172 2004-10-13 2012-02-27 9999-31-12

Day2 i am getting 3 records with diff Eff_dt for the same key columns.

col1 col2 col3 eff_dt
A 41172 2004-10-13 2012-02-28
A 41172 2004-10-13 2012-02-29
A 41172 2004-10-13 2012-02-30

After day-2 ,I need OutPut As:->

OutPut--->day2
col1 col2 col3 Start_dt end_dt
A 41172 2004-10-13 2012-02-27 2012-02-28
A 41172 2004-10-13 2012-02-28 2012-02-29
A 41172 2004-10-13 2012-02-29 2012-02-30
A 41172 2004-10-13 2012-02-30 9999-31-12

As the key columns are same..so next day 1st records
eff_dt will became end_dt for the previous records.
Similarly next records eff_dt will became End_dt for previous row if
Key colums same.otherwise pass "9999-31-12" for last instance of record.


Any suggestion appreciated...

[kandyshandy]

did you try anything? There are couple of ways to achieve this.

SCD; Combination of Lookup & Transformer; ChangeCapture....

[ajay.prakash03]

did you try anything? There are couple of ways to achieve this.

SCD; Combination of Lookup & Transformer; ChangeCapture....

[kandyshandy]

From Transformer, you need to have 2 output links to 2 DB stages. One for UPDATING existing record and another for INSERTING a new record... Try now, derivations will be very straight forward.

Proper paritioning/sorting will play a major role in this job.

[nikhil_bhasin]

Hi Ajay.. did you find any solution for your scenario. I am also facing a similar one.

[chulett]

Nikhil - please start your own post so we can know the details of your 'similar' issue.