Support HubSystem Information Reports
This directory contains SQL scripts and documentation for system-level reports that provide insights into workforce analytics, data retention, system usage, and operational metrics.
Available Reports
Turnover Analysis Reports
| Report | Description | Key Features |
|---|---|---|
| Turnover Percentage by Employee Type | Comprehensive turnover analysis across different employee classifications | Employee type breakdown, Percentage calculations |
| Monthly Turnover Percentage | Month-by-month turnover trends using DAILYSTATE data | Historical trends, DAILYSTATE integration |
| Last 30 Days Turnover | Focused 30-day turnover analysis for specific periods | Short-term analysis, CTE-based calculations |
Peak Employee Reports
| Report | Description | Key Features |
|---|---|---|
| Peak Employee Report | Identifies peak employment dates and provides detailed employee listings by division | Peak date identification, Division-level analysis |
| Peak Employee by Location | Location-based peak analysis with ESS usage metrics | Location focus, ESS integration |
| NewCor Peak Employees | Client-specific peak employee reporting for NewCor | Client reporting, Division summaries |
| NewCor Peak Employee Billing | Billing-focused peak employee reports for NewCor clients | Billing optimization, Location-based |
System Activity Reports
| Report | Description | Key Features |
|---|---|---|
| Clock Transfers in Last 90 Days | Monitors clock transfer activities and employee movement | Transfer tracking, 90-day window |
| Data Retention Analysis | Analyzes storage costs across different retention periods | Cost analysis, Retention periods |
| Workgroup Absence Percentage | Calculates absence rates by workgroup | Absence tracking, Workgroup analysis |
Work-in-Progress Reports
| Report | Description | Key Features |
|---|---|---|
| Working Term Counts (WIP) | Experimental turnover calculation using status history | Historical data, Status tracking |
| Working Active Counts (WIP) | Alternative approach to monthly turnover calculations | Active count focus, Monthly analysis |
Key Features
Data Sources
- EMPLOYEES - Primary employee information and status
- DAILYSTATE - Historical workforce snapshots
- EMPACTSTATUSHIST - Employee status change history
- SUPEDITHIST - Supervisor edit and transfer tracking
- WORKGROUP1/WORKGROUP2 - Location and division hierarchies
- EMPLOYEEACTIVITY - ESS (Employee Self Service) usage tracking
Common Metrics
- Turnover Percentages - Various calculation methodologies
- Peak Employment Dates - Highest workforce levels in periods
- ESS Usage - Employee Self Service adoption rates
- Absence Rates - Workgroup-level attendance patterns
- Data Retention Costs - Storage cost analysis
Technical Approaches
- Complex String Parsing - DAILYSTATE REFDATA processing
- CTE-based Analysis - Modular calculation structures
- Historical Data Integration - Multi-table status tracking
- Date Range Processing - Flexible period analysis
Business Applications
These reports support:
- Workforce Planning - Understanding staffing patterns and trends
- Client Billing - Accurate headcount reporting for billing purposes
- System Monitoring - Tracking system usage and transfer activities
- Cost Management - Data retention and storage cost analysis
- Compliance - Audit trails and historical tracking
- Performance Analysis - Absence rates and productivity metrics
Usage Guidelines
- Date Parameters - Most reports use configurable date ranges
- Workgroup Levels - WG1 (location) vs WG2 (division) organization
- Status Codes - 0 = Active, 1 = Terminated for employee status
- Peak Date Logic - Highest employee count date in specified periods
- ESS Tracking - Employee Self Service usage from EMPLOYEEACTIVITY