Avatar of gs79
gs79
 asked on

Update expired records

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
Oracle Database

Avatar of undefined
Last Comment
awking00

8/22/2022 - Mon
David Johnson, CD

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

ASKER
Yeah we could do null. We have  couple of tables that were designed way back with some future date long time away
slightwv (䄆 Netminder)

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 
/ 

Open 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.

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
slightwv (䄆 Netminder)

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 
/ 

Open in new window

Mark Geerlings

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

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
awking00

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

ASKER
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.
awking00

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'
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes