Employee Personal Transfer Report

Report that displays employee transfer information including workgroup assignments and rate changes for active employees, showing current and transferred workgroup assignments along with rate modification details

Parameters

This script does not require any input parameters.

Data Components

The report consists of the following main sections:

Employee Information - Basic employee identification and name
Workgroup Assignments - Current and transfer workgroup assignments (WG5, WG6, WG7)
Rate Information - Transfer rate and effective date
Rate Flag Description - Type of rate change applied

Output Format

Column	Description
IDNUM	Employee ID number
EmployeeFullName	Employee's full name (Last, First Initial)
Workgroup5Name	Workgroup 5 assignment or "No Transfer / Home WG5"
Workgroup6Name	Workgroup 6 assignment or "No Transfer / Home WG6"
Workgroup7Name	Workgroup 7 assignment name
RATE	Transfer rate amount
EFFDATE	Effective date of the transfer
RateFlagDescription	Type of rate change (Replacement, Addition To, Multiplicative Factor, No Change)

Technical Implementation

The script uses:

EMPTRANRATES table - Source for employee transfer rate information
EMPLOYEES table - Employee master data
WORKGROUP5, WORKGROUP6, WORKGROUP7 tables - Workgroup definitions
LEFT JOINs - To handle cases where workgroups may be 0 (no transfer)
CASE statements - To provide descriptive text for workgroup assignments and rate flags
Conditional JOINs - Only joins workgroup tables when workgroup number is not 0

T-SQL

employee_personal_transfer.sql

SELECT
    E.IDNUM,
    E.LASTNAME + ', ' + E.FIRSTNAME + ' ' + E.INITIAL AS EmployeeFullName,
    CASE
        WHEN ET.WG5 = 0 THEN 'No Transfer / Home WG5' 
        ELSE W5.NAME
    END AS Workgroup5Name,
    CASE
        WHEN ET.WG6 = 0 THEN 'No Transfer / Home WG6' 
        ELSE W6.NAME
    END AS Workgroup6Name,
    W7.NAME AS Workgroup7Name,
    ET.RATE,
    ET.EFFDATE,
    CASE
        WHEN ET.FLAGS = 0 THEN 'Replacement'
        WHEN ET.FLAGS = 1 THEN 'Addition To'
        WHEN ET.FLAGS = 2 THEN 'Multiplcative Factor'
        WHEN ET.FLAGS = 3 THEN 'No Change'
    END AS RateFlagDescription
FROM
    EMPTRANRATES ET
    INNER JOIN
    EMPLOYEES E ON ET.FILEKEY = E.FILEKEY
    LEFT JOIN
    WORKGROUP5 W5 ON ET.WG5 = W5.WGNUM AND ET.WG5 <> 0 -- Only join if ET.WG5 is not 0
    LEFT JOIN
    WORKGROUP6 W6 ON ET.WG6 = W6.WGNUM AND ET.WG6 <> 0 -- Only join if ET.WG6 is not 0
    INNER JOIN
    WORKGROUP7 W7 ON ET.WG7 = W7.WGNUM
WHERE
    E.ACTIVESTATUS = 0
ORDER BY
    EmployeeFullName, Workgroup6Name;

Content Inventory

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