Duplicate Voucher Edit Removal Report

Overview

This utility script identifies and reports duplicate supervisor edits in the SUPEDITS table for specific edit types (BLENDOTCRD, VOUCHER, SALARY2, SALARY3). 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 edit types.

Data Components

The script analyzes four types of supervisor edits:

BLENDOTCRD - Blend overtime card edits
VOUCHER - Voucher-related edits
SALARY2 - Secondary salary edits
SALARY3 - Tertiary salary 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 BLENDOTCRD EDITS - Header and list of duplicate blend overtime edits
DUPLICATE Voucher Edits - Header and list of duplicate voucher edits
DUPLICATE SALARY2 Edits - Header and list of duplicate salary2 edits
DUPLICATE SALARY3 Edits - Header and list of duplicate salary3 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 and EFFDATE to group related edits
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

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
Comprehensive Analysis: Covers four major 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
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 payroll processing and audit requirements
Run during maintenance windows to avoid conflicts with active processing
Backup the SUPEDITS table before running with deletion enabled

T-SQL

duplicate_voucher_edit_removal_report.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF
Declare @BLENDOT Table (UNIQUEID int, Filekey int, EFFDATE Datetime, RowNum Int)
Declare @VOUCHER Table (UNIQUEID int, Filekey int, EFFDATE Datetime, RowNum Int)
Declare @SALARY2 Table (UNIQUEID int, Filekey int, EFFDATE Datetime, RowNum Int)
Declare @SALARY3 Table (UNIQUEID int, Filekey int, EFFDATE Datetime, RowNum Int)
Declare @RemoveEdit Table (UNIQUEID int, Filekey int, Edittype varchar(10), EFFDATE Datetime, RowNum Int)
;
--Changes made on first if statement - s.effdate IN instead of = - partition by filekey and effdate
If Exists (Select s.FILEKEY from SUPEDITS s where s.USERNAME='BLENDOTCRD' Group by s.FILEKEY, s.EFFDATE having Count(*)>1) 
Begin
	Insert into @BLENDOT 
	Select s.UNIQUEID, s.FILEKEY, s.EFFDATE, ROW_NUMBER() Over(Partition by s.filekey, s.effdate order by s.filekey) as RowNum  from SUPEDITS s 
		where s.FILEKEY in (Select s1.FILEKEY from SUPEDITS s1 where s1.USERNAME='BLENDOTCRD' Group by s1.FILEKEY, s1.EFFDATE having Count(*)>1)
		and USERNAME='BLENDOTCRD'
		and s.EFFDATE IN (Select s2.EFFDATE from SUPEDITS s2 where s2.FILEKEY=s.FILEKEY and s2.USERNAME='BLENDOTCRD' Group by s2.FILEKEY, s2.EFFDATE having Count(*)>1)

	Insert into @RemoveEdit
	Select b.UNIQUEID,b.Filekey,'BLENDOTCRD',b.EFFDATE,b.RowNum From @BLENDOT b where b.RowNum!=1
end

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

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

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

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

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

	Insert into @RemoveEdit
	Select s3.UNIQUEID,s3.Filekey,'SALARY3',s3.EFFDATE,s3.RowNum From @SALARY3 s3 where s3.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 BLENDOTCRD EDITS','',1
UNION
Select pw.UNIQUEID, pw.EFFDATE, e.LASTNAME+', '+e.FIRSTNAME as Emp,'',2 from @BLENDOT pw
Inner Join EMPLOYEES e
On pw.Filekey = e.FILEKEY
UNION
Select '','','DUPLICATE Voucher Edits','',3
UNION
Select rg.UNIQUEID, rg.EFFDATE, e.LASTNAME+', '+e.FIRSTNAME as Emp,'',4 from @VOUCHER rg
Inner Join EMPLOYEES e
On rg.Filekey = e.FILEKEY
UNION
Select '','','DUPLICATE SALARY2 Edits','',5
UNION
Select ot.UNIQUEID, ot.EFFDATE, e.LASTNAME+', '+e.FIRSTNAME as Emp,'',6 from @SALARY2 ot
Inner Join EMPLOYEES e
On ot.Filekey = e.FILEKEY
UNION
Select '','','DUPLICATE SALARY3 Edits','',7
UNION
Select dt.UNIQUEID, dt.EFFDATE, e.LASTNAME+', '+e.FIRSTNAME as Emp,'',8 from @SALARY3 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/duplicate_voucher_edit_removal_report.mdx
SQL Script: SQL/utilities/duplicate_voucher_edit_removal_report.sql

Duplicate Voucher Edit Removal Report

On this page