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...
![Smile :)](./images/smilies/icon_smile.gif)