Unused Benefits Last 3 Years Report

This report identifies benefit types that have not been scheduled for any employees in the last 3 years, helping system administrators clean up obsolete benefit configurations and optimize the benefits system.

Overview

The Unused Benefits Last 3 Years Report analyzes the BENEFITS and SCHEDULES tables to find benefit types that haven't been used in benefit schedules over the past three years. This is valuable for system cleanup, identifying redundant benefit configurations, and understanding which benefits are no longer in use.

Parameters

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

Download Alternative Formats

📥 AOD Browser Report Format:

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

Screenshot

Unused Benefits Last 3 Years Report

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

Use Cases

System Cleanup - Identify benefits that can be safely removed from the system
Benefit Analysis - Understand which benefits are no longer relevant to operations
Database Optimization - Clean up unused benefit configurations to improve performance
Audit Compliance - Document unused system components for compliance reviews

Technical Notes

Only analyzes benefit schedules (SCHTYPE = 2) - other schedule types are ignored
Uses a 3-year lookback period from the current execution date
Results are ordered by benefit number for consistent output
JOIN logic ensures only truly unused benefits appear in results

SQL Implementation

unused_benefits_last_3_years.sql

-- Find benefits that have NOT been scheduled in the last 3 years
-- SCHEDULES.SCHTYPE = 2 indicates a benefit schedule
-- SCHEDULES.BENEFIT references BENEFITS.NUM

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

SELECT b.NUM, b.NAME
FROM BENEFITS b
    LEFT JOIN (
    SELECT DISTINCT BENEFIT
    FROM SCHEDULES
    WHERE SCHTYPE = 2 AND SCHDATE >= @ThreeYearsAgo
) s ON b.NUM = s.BENEFIT
WHERE s.BENEFIT IS NULL
ORDER BY b.NUM;

Unused Workgroups Last 3 Years - Identifies unused workgroup configurations
Data Retention Analysis - Analyzes data retention costs and patterns

Content Inventory

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

Unused Benefits Last 3 Years Report

On this page