Unused Workgroups Last 3 Years Report

This report identifies workgroup configurations (levels 1-4) that have not been used in any transactions, pay designations, or schedules in the last 3 years, helping administrators clean up obsolete workgroup structures and optimize system organization.

Overview

The Unused Workgroups Last 3 Years Report analyzes workgroup usage across multiple system tables to identify workgroups that haven't been referenced in the past three years. This comprehensive analysis checks WORKGROUPTRANS, PAYDESHIST, and SCHEDULES tables to ensure accurate identification of truly unused workgroups.

Parameters

• @ThreeYearsAgo - Automatically calculated as 3 years before the current date using DATEADD(YEAR, -3, GETDATE())

Workgroup Levels Analyzed

The report examines four workgroup levels:

1. WORKGROUP1 - Primary workgroup level
2. WORKGROUP2 - Secondary workgroup level
3. WORKGROUP3 - Tertiary workgroup level
4. WORKGROUP4 - Quaternary workgroup level

Note: The report can be extended to include WG5-7 if those tables exist and are needed for your system configuration.

Download Alternative Formats

📥 AOD Browser Report Format:

• Download AOD Import File - Import this report directly into AOD Service Module

Screenshot

💡 Click the image above to zoom in and see the calendar details more clearly!

Use Cases

• System Cleanup - Identify workgroups that can be safely removed or archived
• Organizational Analysis - Understand which workgroup structures are no longer in use
• Database Optimization - Clean up unused workgroup configurations to improve performance
• Structure Audit - Review organizational structures and eliminate redundant configurations
• System Migration - Identify workgroups to exclude when migrating to new systems

Technical Notes

• Analyzes workgroup levels 1-4 only (WG5-7 not included)
• Uses UNION operations to check all three usage tables comprehensively
• 3-year lookback period ensures sufficient historical analysis
• LEFT JOIN logic identifies workgroups with no usage records
• Results are ordered by workgroup level and number for systematic review
• Each workgroup level is analyzed separately with UNION ALL combining results

SQL Implementation

-- Find unused workgroups (levels 1-4) in the last 3 years
-- Checks WORKGROUPTRANS, PAYDESHIST, and SCHEDULES tables for usage
-- Adjust or extend for WG5-7 if those tables exist and are needed

DECLARE @ThreeYearsAgo DATE = DATEADD(YEAR, -3, GETDATE());

-- WG1
    SELECT 'WG1' AS WorkgroupLevel, w1.WGNUM, w1.CODE, w1.NAME
    FROM WORKGROUP1 w1
        LEFT JOIN (
                        SELECT WG1
            FROM WORKGROUPTRANS
            WHERE EVENTDATE >= @ThreeYearsAgo
        UNION
            SELECT WG1
            FROM PAYDESHIST
            WHERE EVENTDATE >= @ThreeYearsAgo
        UNION
            SELECT WG1
            FROM SCHEDULES
            WHERE SCHDATE >= @ThreeYearsAgo
) used ON w1.WGNUM = used.WG1
    WHERE used.WG1 IS NULL

UNION ALL

    -- WG2
    SELECT 'WG2', w2.WGNUM, w2.CODE, w2.NAME
    FROM WORKGROUP2 w2
        LEFT JOIN (
                        SELECT WG2
            FROM WORKGROUPTRANS
            WHERE EVENTDATE >= @ThreeYearsAgo
        UNION
            SELECT WG2
            FROM PAYDESHIST
            WHERE EVENTDATE >= @ThreeYearsAgo
        UNION
            SELECT WG2
            FROM SCHEDULES
            WHERE SCHDATE >= @ThreeYearsAgo
) used ON w2.WGNUM = used.WG2
    WHERE used.WG2 IS NULL

UNION ALL

    -- WG3
    SELECT 'WG3', w3.WGNUM, w3.CODE, w3.NAME
    FROM WORKGROUP3 w3
        LEFT JOIN (
                        SELECT WG3
            FROM WORKGROUPTRANS
            WHERE EVENTDATE >= @ThreeYearsAgo
        UNION
            SELECT WG3
            FROM PAYDESHIST
            WHERE EVENTDATE >= @ThreeYearsAgo
        UNION
            SELECT WG3
            FROM SCHEDULES
            WHERE SCHDATE >= @ThreeYearsAgo
) used ON w3.WGNUM = used.WG3
    WHERE used.WG3 IS NULL

UNION ALL

    -- WG4
    SELECT 'WG4', w4.WGNUM, w4.CODE, w4.NAME
    FROM WORKGROUP4 w4
        LEFT JOIN (
                        SELECT WG4
            FROM WORKGROUPTRANS
            WHERE EVENTDATE >= @ThreeYearsAgo
        UNION
            SELECT WG4
            FROM PAYDESHIST
            WHERE EVENTDATE >= @ThreeYearsAgo
        UNION
            SELECT WG4
            FROM SCHEDULES
            WHERE SCHDATE >= @ThreeYearsAgo
) used ON w4.WGNUM = used.WG4
    WHERE used.WG4 IS NULL
ORDER BY WorkgroupLevel, WGNUM;

Related Reports

• Unused Benefits Last 3 Years - Identifies unused benefit configurations
• Data Retention Analysis - Analyzes data retention costs and patterns
• Workgroup Absence Percentage - Analyzes workgroup-based absence patterns

Content Inventory

• Doc File: content/docs/reports/system_information/unused_workgroups_last_3_years.mdx
• SQL Script: SQL/reports/system_information/unused_workgroups_last_3_years.sql
• Screenshot: public/img/screenshots/reports/system_information/unused_workgroups_last_3_years.jpg