DATA CLEANUP ACTIVITY:  FIRST ROW NOT EQUAL TO 'HIR'

Click here to view the Sample Excel Sheet

Instructions:

1. Run the SQL Provided

    

2. Sort the Output by Job Code

   Student Job Codes

   Convert only terminated student records if the termination date is less than 2 years prior to conversion date (per Conversion Scope).  For this exercise, we suggest excluding terminations prior to 12/31/2017.

   Move all E99 Students (Job Code 1868-1874) to a separate worksheet

   Review and Correct the Students as follows:

   1. Look for any records with a Status (MAX_HIR_STATUS) = A.  The first row must be fixed.
   2. Next, find the records with Status = I with maximum effective dated row > (greater than) 2018.  The first row must be fixed.
   3. Last, find those with Status = I and maximum effective dated row < (less than) 2018.  No action is required, you may skip these.

   Non-Student Job Codes

   Sort by Status ('MAX HR STATUS').  This is found in column 'O'

   Active non-students: All actives must be fixed.  In column 'U', mark as 'Must be fixed'.

   Inactive non-students: For ease of use and to prioritize the work to be done, a formula has been provided for your use in column 'U' in the sample below.  The formula compares the 'MIN' and 'MAX' dates (columns 'C' and 'P') and flags them accordingly.

   For those flagged in column 'U' as 'Last to be reviewed', these are rows that have a minimum and maximum effective termination date that is the same.  It is the campus decision whether to correct or not, as it appears these records were never truly used. These can be moved to a separate worksheet for later review if desired.

   Address all remaining inactive records that are flagged as 'Review and 'Fix' (minimum row DOES NOT equal latest row).

3. Make Your Corrections

   Insert a Hire row using Action/Reason HIR/CNV (Hire/Conversion).

   If your campus converted using the correct Hire date with the Action/Reason of DTA/CNV (Data Conversion), inserting a new row is not required; instead, change the Action to HIR (Hire) using Correction Mode or SQL.

4. Re-Run the SQL

   After the steps have been completed, rerun script and review the remaining records. Confirm that it is OK to NOT convert these records.

   Retain a copy of the SQL for future reference.

   We recommend that you re-run this SQL every 2 or 3 months to determine if any new records need review (i.e., hire row deleted and replaced by Action Reason of TERM/CNL or TERM/ERR). 

Report Sample

End of Article