Employees With No Notifications (PTO Approver Version)

Report that identifies active employees who do not have any notifications configured in the system, customized for organizations that use a PTO Approver custom field

Parameters

This report does not require any parameters. It automatically filters for active employees.

Data Components

The report consists of one main query with four key parts:

Employee Information - Basic employee details and workgroup connections
PTO Approver Subquery - Retrieves the PTO Approver value from custom field ID 2
Notification Subquery - Identifies employees with no notification flags set to 1 or 2
User Access List - Uses FOR XML PATH to concatenate a list of users who have access to each employee

Output Format

The report outputs a detailed list of active employees who have no notification settings.

Column	Description
FullName	Employee's full name (last name, first name)
IDNUM	Employee's ID number
HIRED	Employee's hire date
Location	Workgroup 1 name (location)
Departments	Workgroup 2 name (department)
Shifts	Workgroup 3 name (shift)
PTOApprover	Name of the employee's PTO approver from custom field 2
Users who have access to this employee	Comma-separated list of users with access to the employee

Technical Implementation

The script uses:

LEFT JOIN to identify employees without matching notification records
INNER JOINs to include workgroup hierarchy details
Subquery to retrieve the PTO Approver custom field (FIELDID = 2)
STUFF and FOR XML PATH to create a comma-separated list of users
Filtering for non-administrative users (ACCOUNTTYPE != 2)
Filtering for active employees only (ACTIVESTATUS = 0)

T-SQL

employee_with_no_notifications_ptoapprover.sql

--Used for RAMC who had a customfield for PTOApprover
SELECT 
    E.LASTNAME + ', ' + E.FIRSTNAME AS FullName,
    E.IDNUM,
    E.HIRED,
    W1.NAME as 'Location',
    W2.NAME as 'Departments',
    W3.NAME as 'Shifts',
	(SELECT C.ASSTRING 
     FROM EMPLOYEEFIELDS C 
     WHERE C.FIELDID = 2 AND C.FILEKEY = E.FILEKEY) AS PTOApprover,
    STUFF((
        SELECT ', ' + U.FRIENDLYNAME
        FROM USERACCESS UA
        JOIN USERS U ON UA.USERID = U.UNIQUEID
        WHERE 
            (UA.WG1 = E.WG1 OR UA.WG1 = 0) AND
            (UA.WG2 = E.WG2 OR UA.WG2 = 0) AND
            (UA.WG3 = E.WG3 OR UA.WG3 = 0) AND
			U.ACCOUNTTYPE != 2
        FOR XML PATH('')
    ), 1, 2, '') AS 'Users who have access to this employee'
FROM 
    Employees E
LEFT JOIN (
    SELECT 
        UN.Filekey
    FROM 
        USEREMPNOTIF UN
    WHERE 
        UN.NOTIFFLAG IN (1, 2)
    GROUP BY 
        UN.Filekey
) UNFiltered ON E.Filekey = UNFiltered.Filekey
INNER JOIN WORKGROUP1 W1 ON E.WG1 = W1.WGNUM
INNER JOIN WORKGROUP2 W2 ON E.WG2 = W2.WGNUM
INNER JOIN WORKGROUP3 W3 ON E.WG3 = W3.WGNUM
WHERE 
    UNFiltered.Filekey IS NULL
    AND E.ACTIVESTATUS = 0
ORDER BY 
    E.LASTNAME, E.FIRSTNAME;

Content Inventory

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