Safety Incident Change History

Overview

This utility script processes safety incident edits in the historical supervisor edits table (SUPEDITHIST) for employees in specific workgroups (WG1 = 10, 62) by converting incident change edits (EDITTYPE 601) to employee state edits (EDITTYPE 404). It handles the transformation of historical incident types based on predefined business rules, removes conflicting edits, and ensures proper incident tracking based on installation dates. This script is the historical counterpart to the main safety incident change script.

Parameters

The script operates with hardcoded parameters:

• Target Workgroups: WG1 = 10 (Installation: 6/6/2005), WG1 = 62 (Installation: 3/19/2013)
• Employee Status: Only active employees (ACTIVESTATUS = 0)
• Installation Dates: Used to determine which historical incidents are eligible for processing
• Target Table: SUPEDITHIST (historical supervisor edits)

Data Components

The script performs several key operations:

1. Historical Edit Cleanup - Removes existing employee state edits from history on dates with incident changes
2. Historical Edit Conversion - Converts historical incident change edits (601) to employee state edits (404)
3. Reason Mapping - Maps historical incident types to appropriate reason codes
4. Zero Reason Cleanup - Removes historical employee state edits with reason = 0
5. Results Tracking - Records processing statistics for each employee

Output Format

The script returns processing results for each affected employee:

Technical Implementation

The script uses:

• Cursor-based processing to iterate through eligible employees
• Installation date filtering to ensure proper historical incident calculation
• Dynamic reason code mapping based on historical incident types
• Multi-step edit processing with cleanup and conversion phases
• Results tracking with table variables for audit purposes
• Historical table operations on SUPEDITHIST

Historical Incident Type Mapping

Processing Logic

1. Employee Selection: Process only active employees in target workgroups
2. Installation Check: Only process historical incidents after installation date
3. Conflict Resolution: Remove existing historical employee state edits on same dates
4. Edit Conversion: Convert historical incident change edits to employee state edits
5. Cleanup: Remove invalid historical edits with zero reason codes

Notes

• Historical Processing: Operates on SUPEDITHIST table instead of current SUPEDITS
• Workgroup Specific: Only processes employees in workgroups 10 and 62
• Installation Date Aware: Respects different installation dates per workgroup
• Data Integrity: Maintains proper historical incident tracking and calculation
• Conflict Resolution: Automatically handles conflicting historical edits
• Audit Trail: Provides detailed processing statistics for historical data
• Business Rule Enforcement: Applies standardized incident type mappings to historical records
• Active Employee Focus: Only processes currently active employees
• Historical Consistency: Ensures historical data follows same rules as current data
• Run during maintenance windows to avoid conflicts with historical reporting
• Verify installation dates are correct for your historical environment
• Review incident type mappings to ensure they match historical business rules
• Monitor processing results to identify any unexpected historical patterns
• Test thoroughly before running in production environment
• Consider the impact on historical incident calculations and reporting
• Coordinate with the main safety incident change script for consistency
• Backup SUPEDITHIST table before running this script

T-SQL

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @WG1 INT, @filekey INT;
DECLARE @Results TABLE (
	Filekey INT,
	E404R INT,
	E601To404 INT,
	E404R0 INT
);

DECLARE employees_cursor CURSOR FOR
    SELECT Filekey
FROM EMPLOYEES
WHERE WG1 IN (10, 62)
	AND ACTIVESTATUS = 0
ORDER BY FILEKEY;

OPEN employees_cursor;

FETCH NEXT FROM employees_cursor INTO @filekey;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @WG1 = (SELECT WG1
	FROM EMPLOYEES
	WHERE FILEKEY = @filekey);

	DECLARE @INSTALLATION DATETIME;
	IF @WG1 = 10 
        BEGIN
		SELECT @INSTALLATION = '6/6/2005';
	END;
	IF @WG1 = 62 
        BEGIN
		SELECT @INSTALLATION = '3/19/2013';
	END;

	-- Installation date is needed because incidents before this date won't be calculated   
	-- so an employee state edit will not do anything to the incident   

	DELETE SUPEDITHIST   
    FROM SUPEDITHIST S   
    WHERE S.FILEKEY = @filekey
		AND S.EFFDATE = ISNULL((SELECT effdate
		FROM SUPEDITHIST
		WHERE filekey = @filekey
			AND EDITTYPE = 601
			AND EFFDATE = s.EFFDATE), '1/1/1900')
		AND S.EDITTYPE = 404
		-- AND I.EDITTYPE = 601   
		AND S.EFFDATE >= @INSTALLATION;

	-- Removes existing Employee State edits on the days that have change incidents being inserted   
	INSERT INTO @Results
	SELECT @filekey, @@ROWCOUNT, NULL, NULL;
	-- insert how many edits were deleted.

	UPDATE SUPEDITHIST 
    SET EDITTYPE = 404, -- Edittype 404 is the edittype for Employee State edits   
        PAYDESNUM = 0,   
        PREVPAYDES = 0,   
        REASON = CASE   
            -- Paydesnum is the rule that the incident is changed to. The reason will affect the calculation of the incident   
            WHEN PAYDESNUM = 2 THEN 20 -- Absent w/o Notify   
            WHEN PAYDESNUM = 3 THEN 23 -- Consecutive Absences   
            WHEN PAYDESNUM = 5 THEN 20 -- Tardy w/o Notify   
            WHEN PAYDESNUM = 7 THEN 20 -- Left Early w/o Notify   
            WHEN PAYDESNUM = 9 THEN 22 -- Remove Incident   
            WHEN PAYDESNUM = 10 THEN 21 -- No Incident   
            WHEN PAYDESNUM = 41 THEN 154 -- Abs w Notify  
            WHEN PAYDESNUM = 42 THEN 155 -- Abs wo Notify  
            WHEN PAYDESNUM = 43 THEN 156 -- Consec Abs  
            WHEN PAYDESNUM = 44 THEN 157 -- Tardy w Notify  
            WHEN PAYDESNUM = 45 THEN 158 -- Tardy wo Notify  
            WHEN PAYDESNUM = 46 THEN 159 -- Left Early w Notify  
            WHEN PAYDESNUM = 47 THEN 160 -- Left Early wo Notify  
            WHEN PAYDESNUM = 58 THEN 165 -- Traffic Exclusion
            ELSE 0   
        END   
    WHERE EDITTYPE = 601 -- Edittype 601 is the change incident edit.   
		AND FILEKEY = @filekey
		AND EFFDATE >= @INSTALLATION
		AND PAYDESNUM <> PREVPAYDES;

	UPDATE @Results 
    SET E601To404 = @@ROWCOUNT 
    WHERE Filekey = @filekey;
	-- Insert how many edits were changed from 601 to 404

	DELETE FROM SUPEDITHIST 
    WHERE EDITTYPE = 404
		AND REASON = 0;

	UPDATE @Results 
    SET E404R0 = @@ROWCOUNT 
    WHERE Filekey = @filekey;
	-- Insert how many 0 reason edits were removed.

	FETCH NEXT FROM employees_cursor INTO @filekey;
END;

CLOSE employees_cursor;
DEALLOCATE employees_cursor;

SELECT
	e.LASTNAME + ', ' + e.FIRSTNAME AS Emp,
	e.WG1,
	r.E404R,
	r.E601To404,
	r.E404R0
FROM @Results r
	INNER JOIN EMPLOYEES e ON r.Filekey = e.FILEKEY
ORDER BY 2, 1;

Content Inventory

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