Schedule Edits by Edit Date Report

This report tracks all schedule modifications organized by the date when the edits were made. It provides an audit trail of schedule changes and helps identify patterns in schedule modifications over time.

Parameters

The script likely includes parameters for:

• @StartEditDate - Beginning date for edit tracking
• @EndEditDate - Ending date for edit tracking
• @EmployeeFilter - Optional employee filtering
• @DepartmentFilter - Optional department filtering

Data Components

The report consists of several data sections:

1. Edit Tracking Information - Details about when and who made schedule changes
2. Original vs Modified Data - Comparison of schedule data before and after edits
3. Edit Type Classification - Categories of changes (additions, deletions, modifications)
4. User Activity Summary - Summary of editing activity by user

Output Format

Technical Implementation

The script uses:

• Audit table joins to track schedule modifications
• Date range filtering on edit dates rather than schedule dates
• CASE statements to categorize edit types
• Comparison logic to show before/after values
• User identification through system audit fields
• Temporal queries to reconstruct edit history

Edit Types Tracked

• Schedule Additions - New schedules created
• Schedule Modifications - Changes to existing schedules
• Schedule Deletions - Schedules removed
• Time Changes - Start/end time modifications
• Department Transfers - Work group changes

Use Cases

• Audit trail for schedule changes
• Identifying frequent schedule modifications
• Tracking user activity and editing patterns
• Compliance reporting for schedule management
• Investigating schedule discrepancies
• Performance analysis of scheduling processes
• Training identification for heavy editors

Reporting Benefits

• Accountability - Track who made what changes
• Compliance - Maintain audit trails for regulations
• Quality Control - Identify problematic editing patterns
• Process Improvement - Analyze editing frequency and types

T-SQL

DECLARE @MIN DATE, @MAX DATE;

SET @MIN = '2023-01-01';
SET @MAX = '2023-03-31';

/*
SET @MIN = {mindate};
SET @MAX = {maxdate};
*/

-- Define the filtered SUPEDITS data
WITH CTE_SUPEDITS AS (
    SELECT 
        S.FILEKEY,
        S.TSDATETIME,
        S.EDITTYPE,
        S.EFFDATE,
        U.FRIENDLYNAME AS [USER], -- Explicitly alias this column
        E.LASTNAME + ', ' + E.FIRSTNAME AS EMP
    FROM SUPEDITS S
    INNER JOIN USERS U ON S.USERNAME = U.NAME
    INNER JOIN EMPLOYEES E ON S.FILEKEY = E.FILEKEY
    WHERE 
        CAST(S.TSDATETIME AS DATE) BETWEEN @MIN AND @MAX
        AND S.EDITTYPE IN (22, 23)
),
CTE_SUPEDITHIST AS (
    SELECT 
        SH.FILEKEY,
        SH.TSDATETIME,
        SH.EDITTYPE,
        SH.EFFDATE,
        U.FRIENDLYNAME AS [USER], -- Explicitly alias this column
        E.LASTNAME + ', ' + E.FIRSTNAME AS EMP
    FROM SUPEDITHIST SH
    INNER JOIN USERS U ON SH.USERNAME = U.NAME
    INNER JOIN EMPLOYEES E ON SH.FILEKEY = E.FILEKEY
    WHERE 
        CAST(SH.TSDATETIME AS DATE) BETWEEN @MIN AND @MAX
        AND SH.EDITTYPE IN (22, 23)
        AND NOT EXISTS (
            SELECT 1
            FROM SUPEDITS S
            WHERE S.FILEKEY = SH.FILEKEY
              AND S.TSDATETIME = SH.TSDATETIME
              AND S.EDITTYPE = SH.EDITTYPE
        )
)
-- Combine the results
SELECT 
    [USER],
    EMP,
    CASE 
        WHEN EDITTYPE = 22 THEN 'ADD'
        WHEN EDITTYPE = 23 THEN 'DELETE'
    END AS EDITTYPE,
    FORMAT(EFFDATE, 'M/d/yyyy') AS SchDate,
    FORMAT(TSDATETIME, 'M/d/yyyy hh:mm tt') AS [Edit Date]
FROM CTE_SUPEDITS

UNION ALL

SELECT 
    [USER],
    EMP,
    CASE 
        WHEN EDITTYPE = 22 THEN 'ADD'
        WHEN EDITTYPE = 23 THEN 'DELETE'
    END AS EDITTYPE,
    FORMAT(EFFDATE, 'M/d/yyyy') AS SchDate,
    FORMAT(TSDATETIME, 'M/d/yyyy hh:mm tt') AS [Edit Date]
FROM CTE_SUPEDITHIST

order by 1,2,5

Content Inventory

• Doc File: content/docs/reports/schedule/schedule_edits_by_edit_date.mdx
• SQL Script: SQL/reports/schedule/schedule_edits_by_edit_date.sql