Turnover Percentage Report

This report calculates employee turnover percentages across different employee categories, providing insights into workforce retention and attrition patterns.

Overview

The Turnover Percentage Report analyzes employee termination rates by categorizing employees into different types (All Employees, Full-Time, Part-Time, Hourly, and Salary) and calculating the percentage of terminated employees within each category for a specified date range.

Parameters

Start Date - Currently hardcoded to '1/1/2023'
End Date - Currently hardcoded to '12/31/2023'
Employee Categories - All Employees, Full-Time, Part-Time, Hourly, Salary
Status Tracking - Uses ACTIVESTATUSEFFDATE for effective date filtering

Report Output

The report provides the following information for each employee category:

Employee Type - Category classification (All Emps, Full-Time, Part-Time, Hourly, Salary)
Terminated Count - Number of employees with activestatus = 1 (terminated)
Active Count - Number of employees with activestatus = 0 (active)
Turnover Percentage - Calculated percentage of terminated employees

Employee Classification Logic

Full-Time Employees

Status = 0

Part-Time Employees

Status IN (1,2,3,4)

Hourly Employees

PAYTYPE = 0

Salary Employees

PAYTYPE = 1

Technical Implementation

The script uses:

EMPLOYEES table for employee data and status information
UNION queries to combine results from different employee categories
CASE statements for conditional counting of terminated vs active employees
ROUND function for percentage calculation with 2 decimal precision
ORDER BY clause for consistent result ordering

T-SQL

turn_over_percentage.sql

DECLARE @StartDate DATETIME = '1/1/2023';
DECLARE @EndDate DATETIME = '12/31/2023';

-- Turn Over for all employees
SELECT 
    1 AS order_column,
    'All Emps' as Employee_type,
    COUNT(CASE WHEN activestatus = 1 THEN 1 END) AS terminated_count,
    COUNT(CASE WHEN activestatus = 0 THEN 1 END) AS active_count,
    CAST(ROUND((COUNT(CASE WHEN activestatus = 1 THEN 1 END) * 100.0) / COUNT(*), 2) AS DECIMAL(10, 2)) AS turnover_percentage
FROM 
    employees
WHERE 
    ACTIVESTATUSEFFDATE BETWEEN @StartDate AND @EndDate

UNION

-- Turnover breakdown for full-time employees
SELECT 
    2 AS order_column,
    'Full-Time' AS employee_type,
    COUNT(CASE WHEN activestatus = 1 THEN 1 END) AS terminated_count,
    COUNT(CASE WHEN activestatus = 0 THEN 1 END) AS active_count,
    CAST(ROUND((COUNT(CASE WHEN activestatus = 1 THEN 1 END) * 100.0) / COUNT(*), 2) AS DECIMAL(10, 2)) AS turnover_percentage
FROM 
    employees
WHERE 
    ACTIVESTATUSEFFDATE BETWEEN @StartDate AND @EndDate
    AND status = 0

UNION

-- Turnover breakdown for part-time employees
SELECT 
    3 AS order_column,
    'Part-Time' AS employee_type,
    COUNT(CASE WHEN activestatus = 1 THEN 1 END) AS terminated_count,
    COUNT(CASE WHEN activestatus = 0 THEN 1 END) AS active_count,
    CAST(ROUND((COUNT(CASE WHEN activestatus = 1 THEN 1 END) * 100.0) / COUNT(*), 2) AS DECIMAL(10, 2)) AS turnover_percentage
FROM 
    employees
WHERE 
    ACTIVESTATUSEFFDATE BETWEEN @StartDate AND @EndDate
    AND status IN (1,2,3,4)

UNION

-- Turnover breakdown for Hourly employees
SELECT 
    4 AS order_column,
    'Hourly' AS employee_type,
    COUNT(CASE WHEN activestatus = 1 THEN 1 END) AS terminated_count,
    COUNT(CASE WHEN activestatus = 0 THEN 1 END) AS active_count,
    CAST(ROUND((COUNT(CASE WHEN activestatus = 1 THEN 1 END) * 100.0) / COUNT(*), 2) AS DECIMAL(10, 2)) AS turnover_percentage
FROM 
    employees
WHERE 
    ACTIVESTATUSEFFDATE BETWEEN @StartDate AND @EndDate
    AND PAYTYPE = 0

UNION

-- Turnover breakdown for Salary employees
SELECT 
    5 AS order_column,
    'Salary' AS employee_type,
    COUNT(CASE WHEN activestatus = 1 THEN 1 END) AS terminated_count,
    COUNT(CASE WHEN activestatus = 0 THEN 1 END) AS active_count,
    CAST(ROUND((COUNT(CASE WHEN activestatus = 1 THEN 1 END) * 100.0) / COUNT(*), 2) AS DECIMAL(10, 2)) AS turnover_percentage
FROM 
    employees
WHERE 
    ACTIVESTATUSEFFDATE BETWEEN @StartDate AND @EndDate
    AND PAYTYPE = 1

-- Order the results by the order_column
ORDER BY order_column;

Common Use Cases

HR Analytics - Track workforce retention trends over time
Department Analysis - Compare turnover rates across different employee categories
Strategic Planning - Identify areas needing retention improvement
Compliance Reporting - Monitor workforce stability metrics

Turnover Percentage by Month - Monthly breakdown of turnover rates
Last 30 Days Turnover - Recent turnover analysis
Working Active Counts but Broken Term Counts WIP - Alternative turnover calculation method

Content Inventory

Doc File: content/docs/reports/system_information/turnover_percentage.mdx
SQL Script: SQL/reports/system_information/turn_over_percentage.sql

Turnover Percentage Report

On this page