Employee Status Demographics Report

Comprehensive view of employee demographics and status information for a specified time period, including employee status, workgroup assignments, gender, ethnicity, and EEO classification data with historical tracking

Parameters

@MIN - Start date for the analysis period (example: '6/18/2020')
@MAX - End date for the analysis period (example: '11/1/2018')

Note: The script includes placeholder parameters {mindate} and {maxdate} for integration with reporting tools.

Data Components

The report consists of 5 main sections:

Employee Identification - Basic employee information and current status
Status Information - Active/terminated status with effective dates
Workgroup Assignments - Historical workgroup assignments (WG1, WG2)
Demographics - Gender and ethnicity information
EEO Classification - Equal Employment Opportunity classification

Output Format

Column	Description
FILEKEY	Internal employee file key
Status	Employee status (Active/Terminated)
ACTIVESTATUSEFFDATE	Effective date of current status
Name	Employee's full name (Last, First format)
WG1	Workgroup 1 assignment name
Gender	Employee gender from custom field 8
Ethnicity	Employee ethnicity from custom field 5
WG2	Workgroup 2 assignment name
EEO1	EEO classification from custom field 3

Technical Implementation

The script uses:

Table variables - @WG for storing workgroup history data
CROSS APPLY - To retrieve the most recent workgroup assignments and custom field values
Historical tracking - Combines current employee data with historical workgroup changes
Complex WHERE clause - Handles various employee status scenarios during the date range
EMPLOYEEFIELDS table - Custom field storage for demographics data
EMPHOMEWGHIST table - Historical workgroup assignment tracking
Subqueries - To find the most recent status and workgroup information

T-SQL

employee_status_demographics_report.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE,
		@MAX DATE				
/*		
SET @MIN={mindate}
SET @MAX={maxdate}
*/
SET @MIN='6/18/2020'
SET @MAX='11/1/2018'

DECLARE @WG table (FILEKEY int, EFFDATE datetime, WG1 varchar(30), WG2 varchar(30))

INSERT INTO @WG SELECT e.FILEKEY, e.WGEFFDATE, w1.name, W2.name
FROM EMPLOYEES e, WORKGROUP1 w1, WORKGROUP2 w2  
WHERE e.wg1 = w1.wgnum and e.wg2 = w2.WGNUM

INSERT INTO @WG Select wgh.filekey, wgh.EFFDATE, w1.name, W2.name
from EMPHOMEWGHIST wgh, WORKGROUP1 w1, WORKGROUP2 w2  
WHERE wgh.wg1 = w1.wgnum and wgh.wg2 = w2.WGNUM

select e.FILEKEY, 
Case 
	when e.ACTIVESTATUS = 0 then 'Active'
	else 'Terminated' 
end, 
e.ACTIVESTATUSEFFDATE, e.LASTNAME+', '+e.FIRSTNAME as Name, wg.wg1, g.Gender, eth.Ethnicity, wg.wg2, EEO.EEO1  

from EMPLOYEES e
cross apply
	(Select top 1 WG1, WG2 from @WG where FILEKEY=e.FILEKEY and EFFDATE<=@MIN order by effdate desc) as wg
Cross apply
	(Select asstring as Gender from EMPLOYEEFIELDS where FILEKEY=e.FILEKEY and FIELDID=8) as G
Cross apply
	(Select asstring as Ethnicity from EMPLOYEEFIELDS where FILEKEY=e.FILEKEY and FIELDID=5) as Eth
Cross apply
	(Select asstring as EEO1 from EMPLOYEEFIELDS where FILEKEY=e.FILEKEY and FIELDID=3) as EEO

where 

(((E.ACTIVESTATUS=0) and (E.ACTIVESTATUSEFFDATE<=@MAX)) 
or ((E.ACTIVESTATUSEFFDATE>@MAX) and 
((select top 1 effdate from empactstatushist where empactstatushist.filekey=e.filekey order by effdate desc)<=@MAX) 
and ((select top 1 status from empactstatushist where empactstatushist.filekey=e.filekey order by effdate desc)=0) ) 

or (((select top 1 effdate from empactstatushist where empactstatushist.filekey=e.filekey order by effdate desc)between @min and @MAX) 
and ((select top 1 status from empactstatushist where empactstatushist.filekey=e.filekey order by effdate desc)=1)))

order by 2,5,4

--(e.activestatus = 0) or (e.activestatus = 1 and e.ACTIVESTATUSEFFDATE >= @min)

Content Inventory

Doc File: content/docs/reports/employee_information/employee_status_demographics_report.mdx
SQL Script: SQL/reports/employee_information/employee_status_demographics_report.sql