| Code Help: Inserting Records based on records in another table meeting certain parameters [message #664869] |
Wed, 09 August 2017 08:29  |
 |
cem616
Messages: 3
Registered: August 2017
|
Junior Member |
|
|
I've never scripted before and I have been tasked at work to come up with a script for the following:
We have a table "WRCF_Day". The purpose of this table is to list every single day between DATE_HIRED and DATE_END for all mechanics. So, Mech1 was hired on 1/1/2017 and left on 5/7/2017, it would have an individual record for each date between those two dates. This, and a few other additions can let us see who is scheduled to work days, regardless if they entered in time or not in other unrelated tables. The list of all Mechs, active and inactive, with the DATE_HIRED and DATE_END fields, is in table "SD_CF."
We need to delete records within the last 30 day and then insert in updated records, so we can account for changes, such as employees getting hired or leaving within that time period.
I already scripted to delete records for the last 30 days, going off of "DATE_ASSIGNED" which is the field name for the dates. To insert records, my supervisor and I scripted this:
DECLARE
CURSOR c_cf IS SELECT b.CF_ID FROM AFM_PHILADELPHIA.SD_CF b
WHERE b.DATE_END > trunc(sysdate-30) OR b.DATE_END is null;
r_cf c_cf%ROWTYPE;
BEGIN
FOR i in 1..30 LOOP
OPEN c_cf;
LOOP
FETCH c_cf INTO r_cf;
EXIT WHEN c_cf%NOTFOUND;
INSERT INTO AFM_PHILADELPHIA.SD_WRCF_DAY a (CF_ID, DATE_ASSIGNED)
VALUES (r_cf.CF_ID, sysdate-i);
END LOOP;
CLOSE c_cf;
END LOOP;
END;
If we don't consistently reinsert last 30 days, the table won't get updated if someone leaves within that time period and will keep attributing them to be available to work, even if they have left, or won't include new hires.
Example: Mech 1 is hired 1/1/17 and the table shows every single date for him between 1/1/17 and the current date. Mech1 actually leaves 6/30/17. This will get updated in "SD_CF" under DATE_END. However, "WRCF_DAY" will keep generating a new record for each date because it hasn't been updated with his new DATE_END. So before delete, the table shows Mech 1 records between 1/1/17 and 7/13/17, the last 30 get deleted, and then we want to reinsert in records where the DATE_END is greater than those last 30 days, so it will reflect the fact that he left and only show records between 1/1/7 and 6/30/17.
People are constantly coming and going and we need to update the table accordingly, and there's some 400 mechanics at a time usually, so the script needs to run through each mechanic in "SD_CF" determine is their DATE_HIRED or DATE_END meets our parameters, and then insert the appropriate number of records into "WRCF_Day."
I hope that makes sense. Any help would be great! If there is a better script to insert records based off parameters in another table, I am open to other options.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
