Duplicate Voucher OT Edits

Overview

This utility script identifies duplicate supervisor edits for BLENDOTCRD (blend overtime card) and VOUCHER edit types in the SUPEDITS table. It uses Common Table Expressions (CTEs) to find employees who have multiple edits of the same type on the same effective date, then generates a formatted report showing which employees have duplicate entries. This is a reporting-only script that helps identify data integrity issues without making any changes.

Parameters

This script has no input parameters - it analyzes all supervisor edits for BLENDOTCRD and VOUCHER edit types.

Data Components

The script analyzes two specific supervisor edit types:

1. BLENDOTCRD - Blend overtime card edits
2. VOUCHER - Voucher-related edits

For each type, it identifies duplicates based on:

• Employee (FILEKEY)
• Effective Date (EFFDATE)
• Edit Type (USERNAME)

Output Format

The script returns a formatted report with employee information for duplicate edits:

Report Structure

1. BLENDOTCRD section header (Sort=1)
2. Employee details with duplicate BLENDOTCRD edits (Sort=2)
3. VOUCHER section header (Sort=3)
4. Employee details with duplicate VOUCHER edits (Sort=4)

Technical Implementation

The script uses:

• Common Table Expressions (CTEs) for efficient duplicate detection
• GROUP BY with HAVING to identify records with count > 1
• UNION queries for combined reporting
• JOIN operations to get employee details
• Ordered output for readable report format

Duplicate Detection Logic

1. BLENDOTCRD CTE: Groups by FILEKEY and EFFDATE, finds counts > 1
2. VOUCHER CTE: Groups by FILEKEY and EFFDATE, finds counts > 1
3. Employee Lookup: Joins with EMPLOYEES table for readable names
4. Report Generation: Combines results with section headers

Report Features

• Section Headers: Clear separation between edit types
• Employee Details: Full name and ID number for identification
• Effective Dates: Shows when duplicate edits occurred
• Sorted Output: Organized by section and employee name

Notes

• Report-Only: This script only identifies duplicates, does not remove them
• Two Edit Types: Focuses specifically on BLENDOTCRD and VOUCHER edits
• Employee-Friendly: Shows employee names and IDs for easy identification
• Date-Specific: Identifies duplicates by employee and effective date
• Data Integrity Tool: Helps identify potential data quality issues
• No Data Changes: Safe to run without risk of modifying data
• Formatted Output: Organized report with clear section headers
• Use this script to identify duplicate edits before running cleanup scripts
• Review the results to understand the scope of duplicate data
• Consider the business impact of duplicate edits on payroll processing
• Use the employee information to investigate the source of duplicates
• Run this regularly as part of data quality monitoring
• Coordinate with the more comprehensive duplicate removal scripts for cleanup

T-SQL

WITH BLD AS (
select s.FILEKEY, s.EFFDATE, Count(*) as Count 

from  SUPEDITS s

where 
 s.USERNAME='BLENDOTCRD'

Group by s.FILEKEY, s.EFFDATE having Count(*)>1
),

VOU AS (
select s.FILEKEY, s.EFFDATE, Count(*) as Count 

from  SUPEDITS s

where 
 s.USERNAME='VOUCHER'

Group by s.FILEKEY, s.EFFDATE having Count(*)>1
)

Select 1,'BLENDOTCRD','','',''
UNION
Select 2,e.lastname, e.FIRSTNAME, e.IDNUM, b.EFFDATE

From EMPLOYEES e
Join BLD b
	on e.FILEKEY = b.FILEKEY
UNION
Select 3,'VOUCHER','','',''
UNION
Select 4,e.lastname, e.FIRSTNAME, e.IDNUM, v.EFFDATE

From EMPLOYEES e
Join VOU v
	on e.FILEKEY = v.FILEKEY

Order by 1,2

Content Inventory

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