Data Retention Analysis Report

This script analyzes employee data retention costs across different time periods, providing insights into the financial impact of maintaining historical employee records.

Overview

The Data Retention Analysis Report calculates the estimated storage costs for employee records based on different retention periods (2-10 years). It categorizes employees by their active status and calculates the cost implications of retaining their data for various time periods.

Parameters

@CurrentDate - Current date (automatically set to GETDATE())
@TwoYearsAgo through @TenYearsAgo - Date variables for different retention periods
@CostPerEmployee - Cost per employee record (default: $0.40)

Data Components

The report consists of multiple retention categories:

All Employees - Complete employee dataset regardless of status
2-10 Year Retention Categories - Employees retained within specific time periods
Cost Analysis - Estimated storage costs based on employee count

Output Format

Column	Description
RetentionCategory	Time period category (All, 2 Years, 3 Years, etc.)
TotalEmployees	Total number of employees in the category
ActiveEmployees	Count of currently active employees
TerminatedEmployees	Count of terminated employees
EstimatedCost	Calculated storage cost (TotalEmployees × $0.40)

Technical Implementation

The script uses:

UNION ALL operations to combine different retention scenarios
Date arithmetic with DATEADD function for time period calculations
Conditional aggregation with CASE statements for status categorization
Cost calculation based on configurable per-employee rate
Custom ordering using CASE statement for logical result sequence

T-SQL

data_rentention.sql

-- Declare retention periods
DECLARE @CurrentDate DATE = GETDATE();
DECLARE @TwoYearsAgo DATE = DATEADD(YEAR, -2, @CurrentDate);
DECLARE @ThreeYearsAgo DATE = DATEADD(YEAR, -3, @CurrentDate);
DECLARE @FourYearsAgo DATE = DATEADD(YEAR, -4, @CurrentDate);
DECLARE @FiveYearsAgo DATE = DATEADD(YEAR, -5, @CurrentDate);
DECLARE @SixYearsAgo DATE = DATEADD(YEAR, -6, @CurrentDate);
DECLARE @SevenYearsAgo DATE = DATEADD(YEAR, -7, @CurrentDate);
DECLARE @EightYearsAgo DATE = DATEADD(YEAR, -8, @CurrentDate);
DECLARE @NineYearsAgo DATE = DATEADD(YEAR, -9, @CurrentDate);
DECLARE @TenYearsAgo DATE = DATEADD(YEAR, -10, @CurrentDate);
-- Declare cost per employee
DECLARE @CostPerEmployee DECIMAL(10, 2) = 0.40;
-- Query for retention cost analysis
SELECT
    RetentionCategory,
    COUNT(*) AS TotalEmployees,
    SUM(CASE WHEN ACTIVESTATUS = 0 THEN 1 ELSE 0 END) AS ActiveEmployees,
    SUM(CASE WHEN ACTIVESTATUS = 1 THEN 1 ELSE 0 END) AS TerminatedEmployees,
    COUNT(*) * @CostPerEmployee AS EstimatedCost
FROM (
    -- All employees
                                            SELECT 'All' AS RetentionCategory, ACTIVESTATUS, ACTIVESTATUSEFFDATE
        FROM EMPLOYEES

    UNION ALL
        -- Employees retained within the last 2 years
        SELECT '2 Years' AS RetentionCategory, ACTIVESTATUS, ACTIVESTATUSEFFDATE
        FROM EMPLOYEES
        WHERE ACTIVESTATUS = 0 OR (ACTIVESTATUS = 1 AND ACTIVESTATUSEFFDATE >= @TwoYearsAgo)
    UNION ALL
        -- Employees retained within the last 3 years
        SELECT '3 Years' AS RetentionCategory, ACTIVESTATUS, ACTIVESTATUSEFFDATE
        FROM EMPLOYEES
        WHERE ACTIVESTATUS = 0 OR (ACTIVESTATUS = 1 AND ACTIVESTATUSEFFDATE >= @ThreeYearsAgo)
    UNION ALL
        -- Employees retained within the last 4 years
        SELECT '4 Years' AS RetentionCategory, ACTIVESTATUS, ACTIVESTATUSEFFDATE
        FROM EMPLOYEES
        WHERE ACTIVESTATUS = 0 OR (ACTIVESTATUS = 1 AND ACTIVESTATUSEFFDATE >= @FourYearsAgo)
    UNION ALL
        -- Employees retained within the last 5 years
        SELECT '5 Years' AS RetentionCategory, ACTIVESTATUS, ACTIVESTATUSEFFDATE
        FROM EMPLOYEES
        WHERE ACTIVESTATUS = 0 OR (ACTIVESTATUS = 1 AND ACTIVESTATUSEFFDATE >= @FiveYearsAgo)
    UNION ALL
        -- Employees retained within the last 6 years
        SELECT '6 Years' AS RetentionCategory, ACTIVESTATUS, ACTIVESTATUSEFFDATE
        FROM EMPLOYEES
        WHERE ACTIVESTATUS = 0 OR (ACTIVESTATUS = 1 AND ACTIVESTATUSEFFDATE >= @SixYearsAgo)
    UNION ALL
        -- Employees retained within the last 7 years
        SELECT '7 Years' AS RetentionCategory, ACTIVESTATUS, ACTIVESTATUSEFFDATE
        FROM EMPLOYEES
        WHERE ACTIVESTATUS = 0 OR (ACTIVESTATUS = 1 AND ACTIVESTATUSEFFDATE >= @SevenYearsAgo)
    UNION ALL
        -- Employees retained within the last 8 years
        SELECT '8 Years' AS RetentionCategory, ACTIVESTATUS, ACTIVESTATUSEFFDATE
        FROM EMPLOYEES
        WHERE ACTIVESTATUS = 0 OR (ACTIVESTATUS = 1 AND ACTIVESTATUSEFFDATE >= @EightYearsAgo)
    UNION ALL
        -- Employees retained within the last 9 years
        SELECT '9 Years' AS RetentionCategory, ACTIVESTATUS, ACTIVESTATUSEFFDATE
        FROM EMPLOYEES
        WHERE ACTIVESTATUS = 0 OR (ACTIVESTATUS = 1 AND ACTIVESTATUSEFFDATE >= @NineYearsAgo)
    UNION ALL
        -- Employees retained within the last 10 years
        SELECT '10 Years' AS RetentionCategory, ACTIVESTATUS, ACTIVESTATUSEFFDATE
        FROM EMPLOYEES
        WHERE ACTIVESTATUS = 0 OR (ACTIVESTATUS = 1 AND ACTIVESTATUSEFFDATE >= @TenYearsAgo)
) AS CategorizedEmployees
GROUP BY RetentionCategory
ORDER BY 
    CASE 
        WHEN RetentionCategory = 'All' THEN 1
        WHEN RetentionCategory = '2 Years' THEN 2
        WHEN RetentionCategory = '3 Years' THEN 3
        WHEN RetentionCategory = '4 Years' THEN 4
        WHEN RetentionCategory = '5 Years' THEN 5
        WHEN RetentionCategory = '6 Years' THEN 6
        WHEN RetentionCategory = '7 Years' THEN 7
        WHEN RetentionCategory = '8 Years' THEN 8
        WHEN RetentionCategory = '9 Years' THEN 9
        WHEN RetentionCategory = '10 Years' THEN 10
    END;

Content Inventory

Doc File: content/docs/reports/system_information/data_retention.mdx
SQL Script: SQL/reports/system_information/data_rentention.sql