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
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
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
/
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
/
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?
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?
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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'
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'
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?