Clock Transfers in Last 90 Days

This script provides a quick summary of clock transfer activities that have occurred in the past 90 days, helping administrators monitor system transfer patterns and employee movement.

Overview

The Clock Transfers report analyzes supervisor edit history to identify clock transfer events (edittype = 101) that occurred within the last 90 days. It provides both the total number of transfers and the number of unique employees involved in these transfers.

Parameters

• Date Range - Automatically calculated as last 90 days from current date
• Edit Type - Fixed to 101 (clock transfer events)
• Username Filter - Empty username (system-generated transfers)

Data Components

The report analyzes:

1. Transfer Events - Individual clock transfer occurrences
2. Employee Involvement - Unique employees affected by transfers
3. Time Period - Rolling 90-day window from current date

Output Format

Technical Implementation

The script uses:

• SUPEDITHIST table for supervisor edit history tracking
• Date filtering with DATEADD function for 90-day window
• Edit type filtering to isolate clock transfer events (edittype = 101)
• Username filtering for system-generated transfers (empty username)
• DISTINCT aggregation to count unique employees

Example Usage

-- Run the script to get current 90-day transfer summary
SELECT
    COUNT(*) AS NumberOfTransfers,
    COUNT(DISTINCT Filekey) AS NumberOfEmployees
FROM SUPEDITHIST
WHERE edittype = 101
    AND username = ''
    AND EFFDATE > DATEADD(DAY, -90, GETDATE())

Business Value

This report helps administrators:

• Monitor clock transfer activity patterns
• Identify potential system issues or unusual transfer volumes
• Track employee movement between time clocks
• Ensure proper audit trail for time and attendance transfers
• Support compliance and security monitoring efforts

Related Reports

• Employee transfer history reports
• Supervisor edit activity summaries
• Time clock audit reports

T-SQL

WITH
    FilteredEdits
    AS
    (
        SELECT
            s.FILEKEY
        FROM
            SUPEDITHIST s
        WHERE
        s.edittype = 101
            AND s.username = ''
            AND s.EFFDATE > DATEADD(DAY, -90, GETDATE())
    )
-- Summary row (original counts)
    SELECT
        COUNT(*) AS NumberOfTransfers,
        COUNT(DISTINCT f.FILEKEY) AS NumberOfEmployees,
        NULL AS FILEKEY,
        NULL AS EmployeeName,
        NULL AS Wg2Name,
        NULL AS TransferCount,        
        0 AS SortOrder
    FROM
        FilteredEdits f
UNION
ALL
    -- Detail rows (employees that make up the counts)
    SELECT
        NULL AS NumberOfTransfers,
        NULL AS NumberOfEmployees,
        COALESCE(e.FILEKEY, f.FILEKEY) AS FILEKEY,
        e.lastname + ', ' + e.firstname AS EmployeeName,
        w2.NAME AS Wg2Name,
        COUNT(*) AS TransferCount,
        1 AS SortOrder
    FROM
        FilteredEdits f
        LEFT JOIN EMPLOYEES e ON e.FILEKEY = f.FILEKEY
        LEFT JOIN WORKGROUP2 w2 ON e.WG2 = w2.WGNUM
    GROUP BY
     COALESCE(e.FILEKEY, f.FILEKEY),
     e.lastname,
     e.firstname,
     w2.NAME
ORDER BY
    SortOrder,
    EmployeeName

Content Inventory

• Doc File: content/docs/reports/system_information/clock_transfers_last_90_days.mdx
• SQL Script: SQL/reports/system_information/ClockTransfersInLast90Days.sql