Low Workgroup Code Correction

Overview

This utility script corrects inconsistent workgroup codes in the WORKGROUP5, WORKGROUP6, and WORKGROUP7 tables. It identifies records where the CODE field doesn't match the expected value (WGNUM for groups 5 and 7, NAME for group 6) and updates them to maintain data consistency. The script provides detailed reporting of all changes made during the correction process.

Parameters

This script has no input parameters - it analyzes and corrects all workgroup records based on predefined business rules.

Data Components

The script processes three workgroup tables with different correction rules:

1. WORKGROUP5 - CODE should match WGNUM (numeric code)
2. WORKGROUP6 - CODE should match NAME (descriptive code)
3. WORKGROUP7 - CODE should match WGNUM (numeric code)

For each workgroup, it identifies and corrects:

• Records where CODE doesn't match the expected value
• Records where CODE is NULL

Output Format

The script returns a diagnostic report showing all corrections made:

Report Structure

1. Workgroup 5 section header
2. Individual change descriptions for Workgroup 5
3. Workgroup 6 section header
4. Individual change descriptions for Workgroup 6
5. Workgroup 7 section header
6. Individual change descriptions for Workgroup 7

Technical Implementation

The script uses:

• Table variables for tracking records that need correction
• Conditional UPDATE statements based on workgroup-specific rules
• While loops for processing corrections (though simplified to single iteration)
• UNION queries for comprehensive reporting
• JOIN operations to compare before/after values

Correction Rules

Processing Logic

1. Identification: Find records with incorrect or NULL codes
2. Storage: Store problematic records in table variables
3. Correction: Update codes according to workgroup-specific rules
4. Reporting: Generate detailed change report

Notes

• Data Consistency: Ensures workgroup codes follow standardized rules
• Comprehensive Coverage: Processes all three workgroup tables
• Detailed Reporting: Shows exactly what changes were made
• NULL Handling: Corrects both incorrect and missing codes
• Workgroup-Specific Rules: Applies different logic per workgroup type
• Safe Processing: Uses table variables to track changes
• Audit Trail: Provides complete record of corrections performed
• Run during maintenance windows to avoid conflicts with active operations
• Review the correction rules to ensure they match current business requirements
• Verify workgroup data integrity after script execution
• Consider the impact on reports and processes that depend on workgroup codes
• Test on a copy of the database before running in production
• Monitor for any applications that might depend on the old code values

T-SQL

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

Declare @WG5C int, @WG6C int, @WG7C int

DECLARE @WG5T TABLE(WGNUM smallint, CODE VARCHAR(30), NAME varchar(30))
DECLARE @WG6T TABLE(WGNUM smallint, CODE VARCHAR(30), NAME varchar(30))
DECLARE @WG7T TABLE(WGNUM smallint, CODE VARCHAR(30), NAME varchar(30))

------WORKGROUP 5
INSERT INTO @WG5T Select wg5.WGNUM, wg5.CODE, wg5.NAME from WORKGROUP5 WG5 where wg5.CODE!=Cast(wg5.WGNUM as varchar) or wg5.CODE is null

Set @WG5C = (Select Count(*) from @WG5T)

While @WG5C>0
	Begin
		update WORKGROUP5 set CODE=WGNUM where WGNUM in (Select WGNUM from @WG5T)
		Set @WG5C=0
	End

------WORKGROUP 6
INSERT INTO @WG6T Select wg6.WGNUM, wg6.CODE, wg6.NAME from WORKGROUP6 WG6 where wg6.CODE!=wg6.NAME or wg6.CODE is null

Set @WG6C = (Select Count(*) from @WG6T)

While @WG6C>0
	Begin
		update WORKGROUP6 set CODE=Name where WGNUM in (Select WGNUM from @WG6T)
		Set @WG6C=0
	End

------WORKGROUP 7
INSERT INTO @WG7T Select wg7.WGNUM, wg7.CODE, wg7.NAME from WORKGROUP7 WG7 where wg7.CODE!=Cast(wg7.WGNUM as varchar) or wg7.CODE is null

Set @WG7C = (Select Count(*) from @WG7T)

While @WG7C>0
	Begin
		update WORKGROUP7 set CODE=WGNUM where WGNUM in (Select WGNUM from @WG7T)
		Set @WG7C=0
	End

------DISPLAY RESULTS
Select 'Workgroup 5',1
UNION
Select t5.NAME+' code was changed from ' + t5.CODE +' to '+ w5.code as Diagnostics,2 from @WG5T t5, WORKGROUP5 w5 where t5.WGNUM=w5.WGNUM
UNION
Select Null,3
UNION
Select 'Workgroup 6',4
UNION
Select t6.NAME+' code was changed from ' + t6.CODE +' to '+ w6.code as Diagnostics,5 from @WG6T t6, WORKGROUP6 w6 where t6.WGNUM=w6.WGNUM
UNION
Select Null,6
UNION
Select 'Workgroup 7',7
UNION
Select t7.NAME+' code was changed from ' + t7.CODE +' to '+ w7.code as Diagnostics,8 from @WG7T t7, WORKGROUP7 w7 where t7.WGNUM=w7.WGNUM
order by 2

Content Inventory

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