Payroll ID Custom Field Updater

Script: scripts/utilities/payroll_id_cf_updater.sql

Overview

This utility script updates and maintains payroll ID numbers in the EMPLOYEEHR table's Custom6 field. It processes employee ID numbers by removing prefixes (G, GH, H, PW, PWG, PWL) and leading zeros, then updates or inserts the cleaned ID numbers into the EMPLOYEEHR table for employees in specific workgroups (22, 23, 25, 26).

Parameters

This script does not require input parameters but operates on:

• Target Workgroups: WG2 values 22, 23, 25, 26
• Active Status: Only processes employees with ACTIVESTATUS = 0 (active employees)

Data Components

The script processes data in several stages:

1. ID Number Extraction - Retrieves and cleans IDNUM from EMPLOYEES table
2. Prefix Removal - Removes letter prefixes from ID numbers
3. Leading Zero Removal - Strips leading zeros from cleaned numbers
4. EMPLOYEEHR Updates - Updates existing Custom6 values
5. EMPLOYEEHR Inserts - Inserts missing records
6. Results Summary - Displays count and details of changes made

Output Format

Results are organized as:

• Updated Records count and details
• Inserted Records count and details

Technical Implementation

The script uses:

• Table variables for temporary storage of corrected IDs and results
• REPLACE() functions to remove multiple prefix patterns
• STUFF() and PATINDEX() for leading zero removal
• OUTPUT clauses to capture affected records
• UNION queries to combine and format results
• Conditional logic to handle both updates and inserts

Notes

• Target Workgroups: Only processes employees in WG2 values 22, 23, 25, 26
• Active Employees Only: Filters for ACTIVESTATUS = 0
• Prefix Patterns: Removes G, GH, H, PW, PWG, PWL prefixes
• Leading Zeros: Automatically removes leading zeros from cleaned IDs
• Dual Operation: Both updates existing records and inserts missing ones
• Results Display: Shows counts and details of all changes made
• Data Integrity: Uses OUTPUT clauses to track all modifications
• Run during maintenance windows as it modifies employee data
• Review results carefully before committing in production environments

T-SQL

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @UpdatedRecords TABLE (Ownerid INT,
    Custom6 VARCHAR(20))
DECLARE @InsertedRecords TABLE (Ownerid INT,
    Custom6 VARCHAR(20))
DECLARE	@CorrectedIDNum Table(Filekey int,
    IDNUM Varchar(20))

--Grab IDnums from employees without Letters in front of the number.
Insert INTO @CorrectedIDNum
Select e.FILEKEY, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(e.IDNUM, 'G', ''), 'GH', ''), 'H', ''), 'PW', ''), 'PWG', ''), 'PWL', '')
FROM EMPLOYEES e
WHERE e.ACTIVESTATUS = 0 AND e.WG2 IN (22, 23, 25, 26)

--Remove Leading Zero's
UPDATE @CorrectedIDNum
SET IDNUM = STUFF(IDNUM, 1, PATINDEX('%[^0]%', IDNUM) - 1, '')

-- Update EMPLOYEEHR
UPDATE eh
SET Custom6 = cn.IDNUM
OUTPUT INSERTED.Ownerid, INSERTED.Custom6 INTO @UpdatedRecords
FROM EMPLOYEEHR eh
    INNER JOIN @CorrectedIDNum cn ON eh.Ownerid = cn.Filekey
WHERE eh.Custom6 <> cn.IDNUM OR eh.Custom6 IS NULL

-- Insert missing info into EMPLOYEEHR
INSERT INTO EMPLOYEEHR
    (Ownerid, Custom6)
OUTPUT INSERTED.Ownerid, INSERTED.Custom6 INTO @InsertedRecords
SELECT cn.Filekey, cn.IDNUM
FROM @CorrectedIDNum cn
WHERE NOT EXISTS (SELECT 1
FROM EMPLOYEEHR
WHERE Ownerid = cn.Filekey)

-- Display the results
    SELECT 'Updated Records' AS FileKey, 'Count: ' + CAST(COUNT(*) as Varchar) AS ID, 1
    FROM @UpdatedRecords
UNION
    Select CAST(UR.Ownerid as Varchar), UR.Custom6, 2
    from @UpdatedRecords UR
UNION
    SELECT 'Inserted Records' AS Filekey, 'Count: ' + CAST(COUNT(*) as Varchar) AS ID, 3
    FROM @InsertedRecords
UNION
    Select CAST(IR.Ownerid as Varchar), IR.Custom6, 4
    From @InsertedRecords IR

Order by 3

Content Inventory

• Doc File: content/docs/utilities/payroll_id_cf_updater.mdx
• SQL Script: SQL/utilities/payroll_id_cf_updater.sql