insert into new row with max of effdate
Posted: Sat Oct 31, 2009 8:23 am
Suppose an employee has 3 Effective dated rows in xx table
JOB.EFFDATEs:
1/1/2007
1/1/2008
1/1/2009
And the last_sal_change_date is 5/1/2008. So in this case the last_sal_change_date does not match to any of the JOB.EFFDT.
So we need to fetch the Max JOB.EFFDT that is <= last_sal_change_date and in the example above this will be 1/1/2008.
We need to copy the data for EFFDT 1/1/2008 and change the EFFDT to 5/1/2008 (last_sal_change_date) ..
please help design the job ...thanks
JOB.EFFDATEs:
1/1/2007
1/1/2008
1/1/2009
And the last_sal_change_date is 5/1/2008. So in this case the last_sal_change_date does not match to any of the JOB.EFFDT.
So we need to fetch the Max JOB.EFFDT that is <= last_sal_change_date and in the example above this will be 1/1/2008.
We need to copy the data for EFFDT 1/1/2008 and change the EFFDT to 5/1/2008 (last_sal_change_date) ..
please help design the job ...thanks