Last 30 Days Turnover Report

This script calculates the employee turnover percentage for a specific 30-day period, providing a focused view of workforce stability during a defined timeframe.

Overview

The Last 30 Days Turnover Report uses Common Table Expressions (CTEs) to calculate turnover percentage by comparing employees who left during a 30-day period against the total active workforce at the beginning of that period. The script currently uses a fixed date range but can be modified for different periods.

Parameters

Start Date - Currently hardcoded to October 2, 2021 (30 days before end date)
End Date - Currently hardcoded to November 1, 2021
Period Length - 30 days (configurable by modifying DATEADD parameters)

Data Components

The report consists of two main calculations:

Terminations CTE - Counts employees who were terminated during the 30-day period
ActiveBefore CTE - Counts employees who were active before the period started
Turnover Calculation - Percentage calculation based on leavers vs. active workforce

Output Format

Column	Description
turnover_percentage	Calculated turnover percentage rounded to 3 decimal places

Technical Implementation

The script uses:

Common Table Expressions (CTEs) for modular calculation structure
Date range filtering with BETWEEN clause for precise period definition
Active status filtering (ACTIVESTATUS = 1 for terminated, 0 for active)
Percentage calculation with FLOAT casting for precision
ROUND function for result formatting to 3 decimal places

T-SQL

last_30_days_turn_over.sql

WITH Terminations AS (
    SELECT COUNT(*) AS leavers
    FROM employees
    WHERE ACTIVESTATUS = 1 
    AND ACTIVESTATUSEFFDATE BETWEEN DATEADD(day, -30, '2021-11-1') AND '2021-11-1'
),
ActiveBefore AS (
    SELECT COUNT(*) AS total_before
    FROM employees
    WHERE (ACTIVESTATUS = 0 AND ACTIVESTATUSEFFDATE < DATEADD(day, -30, '2021-11-1'))
)

SELECT 
    ROUND((CAST(leavers AS FLOAT) / CAST(total_before AS FLOAT)) * 100, 3) AS turnover_percentage
FROM Terminations, ActiveBefore;

Content Inventory

Doc File: content/docs/reports/system_information/last_30_days_turnover.mdx
SQL Script: SQL/reports/system_information/last_30_days_turn_over.sql

Last 30 Days Turnover Report

On this page