Update expired records

empid   salary_rt   eff_dt                  start_dt               end_dt                                     valid fl

100       abc          10/05/2014         10/05/2014       06/04/2015                             N
100       lmn          06/04/2015         06/04/2015       05/10/2019                             N
100       pqr           05/10/2019         05/10/2019       null                                           Y
200       lkd           04/02/2018          04/02/2018      05/20/2019                             N
200       fjk            05/20/2019          05/20/2019      null                                           Y

Should it not be like this?

Yeah we could do null. We have  couple of tables that were designed way back with some future date long time away

You don't show what the data looks like before the update so I had to guess a little.

No need for PL/SQL.  Straight SQL should work.

See if this works for you:

merge into tab1 t1 
using( 
	select empid, salary_rt, row_number() over(partition by empid order by eff_dt desc) rn 
	from tab1 
) t2 
on (t1.empid=t2.empid and t1.salary_rt=t2.salary_rt) 
when matched then update 
	set 
		t1.end_dt=case when rn=1 then to_date('12/31/9999','MM/DD/YYYY') else t1.end_dt end, 
		t1.valid=case when rn=1 then 'Y' else 'N' end 
/ 

Select allOpen in new window

It assumes empid and salary_rt are distinct.  If not, you'll need to change the columns in the 'ON' clause of the MERGE.

Using eff_dt instead of salary_rt:

merge into tab1 t1 
using( 
	select empid, eff_dt, row_number() over(partition by empid order by eff_dt desc) rn 
	from tab1 
) t2 
on (t1.empid=t2.empid and t1.eff_dt=t2.eff_dt) 
when matched then update 
	set 
		t1.end_dt=case when rn=1 then to_date('12/31/9999','MM/DD/YYYY') else t1.end_dt end, 
		t1.valid=case when rn=1 then 'Y' else 'N' end 
/ 

Select allOpen in new window

Please clarify if you want "to update the audit columns *after* [the] initial load"
or *during" the initial load.

Also, can you give us an example of what you want the records to look like after they are updated?

Thanks for the responses. It is 'After' the initial load.

Start Dt is always eff_dt
end dt = future dt 12/31/9999 for the most recent record where valid fl = Y, or rn = 1 as in slightwv's code
end dt = eff_dt of the next record.

The SQL slightwv provided is not closing out the expired records where fl = N (as highlighted in  Bold)

The table should look as follows after running the script
empid   salary_rt   eff_dt                  start_dt               end_dt                                     valid fl

100       abc          10/05/2014         10/05/2014       06/04/2015                             N
100       lmn          06/04/2015         06/04/2015       05/10/2019                             N
100       pqr           05/10/2019         05/10/2019       12/31/9999                                        Y
200       lkd           04/02/2018          04/02/2018      05/20/2019                             N
200       fjk            05/20/2019          05/20/2019      12/31/9999                                        Y

Let us knowis is correct
Using your first record, let me see if I can replicate how the sequence of inserts and updates would be treated -
Initial insert -
100       abc          10/05/2014
after audit update
100       abc          10/05/2014         10/05/2014       12/31/9999                             Y
after next insert -
100       abc          10/05/2014         10/05/2014       12/31/9999                             Y
100       lmn          06/04/2015        
after audit update
100       abc          10/05/2014         10/05/2014       06/04/2015                             N
100       lmn          06/04/2015         06/04/2015       12/31/9999                             Y
after final insert
100       abc          10/05/2014         10/05/2014       06/04/2015                             N
100       lmn          06/04/2015         06/04/2015       12/31/9999                             Y
100       pqr           05/10/2019        
after latest audit update
100       abc          10/05/2014         10/05/2014       06/04/2015                             N
100       lmn          06/04/2015         06/04/2015       05/10/2019                             N
100       pqr           05/10/2019         05/10/2019       12/31/9999                             Y

Yes. The audit column values are incorrectly populated. It is not blank. End date is defaulting to 12/31/9999 and Y in the initial load even if there are multiple records for empid. Even incrementally it is over writing all the records to default value..probably due to bug in the functionality of the tool or I could be setting it up wrong. I need a post process sql update to fix the audit columns. Since these are small tables, it will probably not much expensive. I think slightwv's sql is updating the records as per I want.

So the initial insert I used above would really have looked this?
100       abc          10/05/2014         10/05/2014       12/31/9999                             Y
and after the next insert would have looked like this
100       abc          10/05/2014         10/05/2014       12/31/9999                             Y
100       lmn          06/04/2015         06/04/2015       12/31/9999                             Y
and the after the update like this
100       abc          10/05/2014         10/05/2014       06/04/2015                             N  ==> so only this record would require the update?
100       lmn          06/04/2015         06/04/2015       12/31/9999                             Y


This would actually simplify things a little -
Using slightwv's merge statement, set end_dt = next_eff_dt, valid_fl = 'N'
where rn = 2 and valid_fl = 'Y'