We help IT Professionals succeed at work.

Update expired records

gs79
gs79 asked
on
I have a records loaded from a tool into a table as follows. The audit columns start_dt and end_dt and valid_fl is not loaded if the multiple records exists for the same empid. But after initial the audit column value is inserted properly. I am trying to come up with a script to update the audit columns after intitial load as followsas follows

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 (Future date)     Y
200       lkd           04/02/2018          04/02/2018      05/20/2019                             N
200       fjk            05/20/2019          05/20/2019      12/31/9999 (Future date)      Y

Is there a way to do it in plsql?

Thanks
Comment
Watch Question

View Solution Only

David Johnson, CDSimple Geek from the '70s
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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?
gs79

Author

Commented:
Yeah we could do null. We have  couple of tables that were designed way back with some future date long time away
slightwv (䄆 Netminder)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

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 
/


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

slightwv (䄆 Netminder)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

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 
/
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
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?
gs79

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:

Without "before" data to go with the "after" data, it is hard to get all the requirements.


See if this gets what you need:

merge into tab1 t1 
using( 
	select empid, eff_dt, 
		lead(eff_dt) over(partition by empid order by eff_dt asc) next_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 t2.next_eff_dt end, 
		t1.valid=case when rn=1 then 'Y' else 'N' end 
/
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
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
gs79

Author

Commented:
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.
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
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'