Link to home
Start Free TrialLog in
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
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

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?
Avatar of gs79
gs79

ASKER

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 
/ 

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.

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

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?
Avatar of 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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of 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.
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'