Employees With No Notifications

Report that identifies active employees who do not have any notifications configured in the system, providing detailed employee information including workgroup hierarchy and user access lists

Parameters

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

Data Components

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

Employee Information - Basic employee details and workgroup connections
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)
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
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.sql

SELECT 
    E.LASTNAME + ', ' + E.FIRSTNAME AS FullName,
    E.IDNUM,
    E.HIRED,
    W1.NAME as 'Location',
    W2.NAME as 'Departments',
    W3.NAME as 'Shifts',
    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.mdx
SQL Script: SQL/reports/employee_information/employee_with_no_notifications.sql