Aggregated Exceptions Report

Comprehensive view of employee attendance exceptions and paid time off within a specified date range, aggregating various exception types alongside excused absences and sick time

Parameters

@MIN - Start date for the report period (example: '2024-08-04')
@MAX - End date for the report period (example: '2024-08-10')

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

Data Components

The report consists of 3 main sections:

Employee Information - Basic employee identification and name
Paid Time Off - Excused absences and sick time hours
Exception Aggregations - Counts and amounts for various attendance exceptions

Output Format

Column	Description
IDNUM	Employee ID number
EmployeeName	Employee's full name (Last, First Initial)
EVENTDATE	Date of the event
ExcusedAbs	Hours of excused absence (Pay Designation 8)
Sick	Hours of sick time (Pay Designation 9)
Early	Amount for early arrival exceptions
Tardy	Amount for tardy exceptions
LeftEarly	Amount for leaving early exceptions
LeftLate	Amount for leaving late exceptions
Unscheduled	Amount for unscheduled time exceptions
Absent	Amount for absent exceptions

Technical Implementation

The script uses:

Common Table Expression (CTE) - AggregatedExceptions to summarize exception data
CASE statements with SUM - To aggregate specific exception types by number
INNER and LEFT JOINs - To combine employee, pay designation, and exception data
GROUP BY - To aggregate data by employee and date
Date range filtering - Using BETWEEN clause for the specified period
SET NOCOUNT ON and ANSI_WARNINGS OFF - For performance optimization

T-SQL

aggregated_exceptions.sql

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;
DECLARE @MIN DATE, @MAX DATE;
Set @MIN = {mindate};
Set @MAX = {maxdate};
/*
SET @MIN = '2024-08-04';
SET @MAX = '2024-08-10';
*/
WITH
    AggregatedExceptions
    AS
    (
        SELECT
            FILEKEY,
            EVENTDATE,
            SUM(CASE WHEN EXCEPTION = 1 THEN AMOUNT ELSE 0 END) AS Early,
            SUM(CASE WHEN EXCEPTION = 2 THEN AMOUNT ELSE 0 END) AS Tardy,
            SUM(CASE WHEN EXCEPTION = 3 THEN AMOUNT ELSE 0 END) AS LeftEarly,
            SUM(CASE WHEN EXCEPTION = 4 THEN AMOUNT ELSE 0 END) AS LeftLate,
            SUM(CASE WHEN EXCEPTION = 9 THEN AMOUNT ELSE 0 END) AS Unscheduled,
            SUM(CASE WHEN EXCEPTION = 10 THEN AMOUNT ELSE 0 END) AS Absent
        FROM EXCEPTIONHIST
        GROUP BY FILEKEY, EVENTDATE
    )
SELECT
    E.IDNUM,
    E.LASTNAME + ', ' + E.FIRSTNAME + ' ' + ISNULL(E.INITIAL, '') AS EmployeeName,
    pd.EVENTDATE,
    SUM(CASE WHEN pd.PAYDESNUM = 8 THEN pd.HOURS ELSE 0 END) AS ExcusedAbs,
    SUM(CASE WHEN pd.PAYDESNUM = 9 THEN pd.HOURS ELSE 0 END) AS Sick,
    ae.Early,
    ae.Tardy,
    ae.LeftEarly,
    ae.LeftLate,
    ae.Unscheduled,
    ae.Absent
FROM
    EMPLOYEES e
    INNER JOIN
    PAYDESHIST pd ON e.FILEKEY = pd.FILEKEY
    LEFT JOIN
    AggregatedExceptions ae ON e.FILEKEY = ae.FILEKEY AND pd.EVENTDATE = ae.EVENTDATE
WHERE 
    pd.EVENTDATE BETWEEN @MIN AND @MAX
GROUP BY 
    E.IDNUM,
    E.FIRSTNAME,
    E.LASTNAME,
    E.INITIAL,
    pd.EVENTDATE,
    ae.Early,
    ae.Tardy,
    ae.LeftEarly,
    ae.LeftLate,
    ae.Unscheduled,
    ae.Absent
ORDER BY 2, 3;

Content Inventory

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