MIT Turkey Duplicate Edit Removal Report

Overview

This utility script identifies and reports duplicate supervisor edits in the SUPEDITS table for specific MIT Turkey-related edit types (PAWEEK, WPABONUSRG, WPABONUSOT, WPABONUSDT). It detects when multiple edits exist for the same employee and effective date, identifies which duplicates should be removed (keeping only the first occurrence), and provides a comprehensive report of all duplicates found. The script includes commented-out deletion logic for actual cleanup when needed.

Parameters

This script has no input parameters - it analyzes all supervisor edits for the specified MIT Turkey edit types.

Data Components

The script analyzes four types of MIT Turkey supervisor edits:

PAWEEK - PA week edits
WPABONUSRG - WPA bonus regular hours edits
WPABONUSOT - WPA bonus overtime hours edits
WPABONUSDT - WPA bonus double-time hours edits

For each type, it identifies duplicates based on:

Employee (FILEKEY)
Effective Date (EFFDATE)
Username/Edit Type

Output Format

The script returns a comprehensive report with multiple sections:

Column1	Column2	Column3	Column4	Column5
UNIQUEID	EFFDATE	Employee Name/Section	EditType	Sort

Report Sections (in order)

DUPLICATE PAWEEK EDITS - Header and list of duplicate PA week edits
DUPLICATE Reg Edits - Header and list of duplicate regular bonus edits
DUPLICATE OT Edits - Header and list of duplicate overtime bonus edits
DUPLICATE DT Edits - Header and list of duplicate double-time bonus edits
EDITS THAT WERE REMOVED - Header and list of edits marked for removal

Technical Implementation

The script uses:

Table variables for temporary storage of duplicate analysis
ROW_NUMBER() window function to identify duplicate occurrences
Partition by FILEKEY to group related edits by employee
Conditional existence checks to only process edit types with duplicates
UNION queries for comprehensive reporting
Employee name joins for readable output

Duplicate Detection Logic

Identification: Find employees with multiple edits of same type on same date
Ranking: Use ROW_NUMBER() to rank duplicates (1st, 2nd, 3rd, etc.)
Marking: Mark all but the first occurrence (RowNum != 1) for removal
Reporting: Display all duplicates and removal candidates

Edit Type Mapping

Username	Report Label	Description
PAWEEK	PAWEEK	PA week edits
WPABONUSRG	PAREGHRS	Regular hours bonus edits
WPABONUSOT	PAOTHRS	Overtime hours bonus edits
WPABONUSDT	PADTHRS	Double-time hours bonus edits

Safety Features

Report-Only Mode: Deletion logic is commented out by default
Comprehensive Logging: Shows all duplicates before any removal
Employee Identification: Includes employee names for verification

Notes

Report-Only by Default: Deletion logic is commented out for safety
MIT Turkey Specific: Covers four specific MIT Turkey edit types
Duplicate Preservation: Always keeps the first occurrence of duplicates
Employee Verification: Includes employee names for manual verification
Batch Processing: Handles multiple edit types in single execution
Data Integrity: Maintains referential integrity during cleanup
Audit Trail: Provides detailed reporting before any changes
Bonus Hour Focus: Specifically targets bonus hour calculations
Review the report output carefully before enabling deletion
Test on a database copy before running in production
Verify that keeping the "first" duplicate is the correct business rule
Consider the impact on MIT Turkey bonus calculations
Run during maintenance windows to avoid conflicts with active processing
Backup the SUPEDITS table before running with deletion enabled
Coordinate with payroll team before removing any bonus-related edits

T-SQL

miturkey_duplicate_edit_removal_report.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF
Declare @PAWEEK Table (UNIQUEID int, Filekey int, EFFDATE Datetime, RowNum Int)
Declare @WPABONUSRG Table (UNIQUEID int, Filekey int, EFFDATE Datetime, RowNum Int)
Declare @WPABONUSOT Table (UNIQUEID int, Filekey int, EFFDATE Datetime, RowNum Int)
Declare @WPABONUSDT Table (UNIQUEID int, Filekey int, EFFDATE Datetime, RowNum Int)
Declare @RemoveEdit Table (UNIQUEID int, Filekey int, Edittype varchar(10), EFFDATE Datetime, RowNum Int)
;

If Exists (Select s.FILEKEY from SUPEDITS s where s.USERNAME='PAWEEK' Group by s.FILEKEY, s.EFFDATE having Count(*)>1) 
Begin
	Insert into @PAWEEK 
	Select s.UNIQUEID, s.FILEKEY, s.EFFDATE, ROW_NUMBER() Over(Partition by s.Filekey order by s.filekey) as RowNum  from SUPEDITS s 
		where s.FILEKEY in (Select s1.FILEKEY from SUPEDITS s1 where s1.USERNAME='PAWEEK' Group by s1.FILEKEY, s1.EFFDATE having Count(*)>1)
		and USERNAME='PAWEEK'
		and s.EFFDATE = (Select s2.EFFDATE from SUPEDITS s2 where s2.FILEKEY=s.FILEKEY and s2.USERNAME='PAWEEK' Group by s2.FILEKEY, s2.EFFDATE having Count(*)>1)

	Insert into @RemoveEdit
	Select pa.UNIQUEID,pa.Filekey,'PAWEEK',pa.EFFDATE,pa.RowNum From @PAWEEK pa where RowNum!=1
end

If Exists (Select s.FILEKEY from SUPEDITS s where s.USERNAME='WPABONUSRG' Group by s.FILEKEY, s.EFFDATE having Count(*)>1) 
Begin
	Insert into @WPABONUSRG 
	Select s.UNIQUEID, s.FILEKEY, s.EFFDATE, ROW_NUMBER() Over(Partition by s.Filekey order by s.filekey) as RowNum from SUPEDITS s 
		where s.FILEKEY in (Select s1.FILEKEY from SUPEDITS s1 where s1.USERNAME='WPABONUSRG' Group by s1.FILEKEY, s1.EFFDATE having Count(*)>1)
		and USERNAME='WPABONUSRG'
		and s.EFFDATE = (Select s2.EFFDATE from SUPEDITS s2 where s2.FILEKEY=s.FILEKEY and s2.USERNAME='WPABONUSRG' Group by s2.FILEKEY, s2.EFFDATE having Count(*)>1)

	Insert into @RemoveEdit
	Select rg.UNIQUEID,rg.Filekey,'PAREGHRS',rg.EFFDATE,rg.RowNum From @WPABONUSRG rg where RowNum!=1
end

If Exists (Select s.FILEKEY from SUPEDITS s where s.USERNAME='WPABONUSOT' Group by s.FILEKEY, s.EFFDATE having Count(*)>1) 
Begin
	Insert into @WPABONUSOT 
	Select s.UNIQUEID, s.FILEKEY, s.EFFDATE, ROW_NUMBER() Over(Partition by s.Filekey order by s.filekey) as RowNum from SUPEDITS s 
		where s.FILEKEY in (Select s1.FILEKEY from SUPEDITS s1 where s1.USERNAME='WPABONUSOT' Group by s1.FILEKEY, s1.EFFDATE having Count(*)>1)
		and USERNAME='WPABONUSOT'
		and s.EFFDATE = (Select s2.EFFDATE from SUPEDITS s2 where s2.FILEKEY=s.FILEKEY and s2.USERNAME='WPABONUSOT' Group by s2.FILEKEY, s2.EFFDATE having Count(*)>1)

	Insert into @RemoveEdit
	Select ot.UNIQUEID,ot.Filekey,'PAOTHRS',ot.EFFDATE,ot.RowNum From @WPABONUSOT ot where RowNum!=1
end

If Exists (Select s.FILEKEY from SUPEDITS s where s.USERNAME='WPABONUSDT' Group by s.FILEKEY, s.EFFDATE having Count(*)>1) 
Begin
	Insert into @WPABONUSDT
	Select s.UNIQUEID, s.FILEKEY, s.EFFDATE, ROW_NUMBER() Over(Partition by s.Filekey order by s.filekey) as RowNum from SUPEDITS s 
		where s.FILEKEY in (Select s1.FILEKEY from SUPEDITS s1 where s1.USERNAME='WPABONUSDT' Group by s1.FILEKEY, s1.EFFDATE having Count(*)>1)
		and USERNAME='WPABONUSDT'
		and s.EFFDATE = (Select s2.EFFDATE from SUPEDITS s2 where s2.FILEKEY=s.FILEKEY and s2.USERNAME='WPABONUSDT' Group by s2.FILEKEY, s2.EFFDATE having Count(*)>1)

	Insert into @RemoveEdit
	Select dt.UNIQUEID,dt.Filekey,'PADTHRS',dt.EFFDATE,dt.RowNum From @WPABONUSDT dt where RowNum!=1
end

/*
If (Select Count(*) from @RemoveEdit)>0
Begin
	Delete from SUPEDITS where UNIQUEID In (Select re.UNIQUEID From @RemoveEdit RE)
End
*/
Select '','','DUPLICATE PAWEEK EDITS','',1
UNION
Select pw.UNIQUEID, pw.EFFDATE, e.LASTNAME+', '+e.FIRSTNAME as Emp,'',2 from @PAWEEK pw
Inner Join EMPLOYEES e
On pw.Filekey = e.FILEKEY
UNION
Select '','','DUPLICATE Reg Edits','',3
UNION
Select rg.UNIQUEID, rg.EFFDATE, e.LASTNAME+', '+e.FIRSTNAME as Emp,'',4 from @WPABONUSRG rg
Inner Join EMPLOYEES e
On rg.Filekey = e.FILEKEY
UNION
Select '','','DUPLICATE OT Edits','',5
UNION
Select ot.UNIQUEID, ot.EFFDATE, e.LASTNAME+', '+e.FIRSTNAME as Emp,'',6 from @WPABONUSOT ot
Inner Join EMPLOYEES e
On ot.Filekey = e.FILEKEY
UNION
Select '','','DUPLICATE DT Edits','',7
UNION
Select dt.UNIQUEID, dt.EFFDATE, e.LASTNAME+', '+e.FIRSTNAME as Emp,'',8 from @WPABONUSDT dt
Inner Join EMPLOYEES e
On dt.Filekey = e.FILEKEY
UNION
Select '','','EDITS THAT WERE REMOVED','',9
UNION
Select re.UNIQUEID, re.EFFDATE, e.LASTNAME+', '+e.FIRSTNAME as Emp,re.Edittype,10 from @RemoveEdit re
Inner Join EMPLOYEES e
On re.Filekey = e.FILEKEY

order by 5

Content Inventory

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

MIT Turkey Duplicate Edit Removal Report

On this page