Benefit Balance As Of Date

Overview

The "Benefit Balance As Of Date" report provides a comprehensive view of employee benefit balances at a specific point in time. This report calculates the current balance for each employee's benefits (Vacation, Sick Time, Personal) by considering the starting balance, manual edits, and benefit usage up to the specified date.

Benefit Balance As Of Date Report

Key Features

Point-in-Time Balance: Shows benefit balances as of a specific date
Comprehensive Calculation: Includes starting balance, manual edits, and benefit usage
Multiple Benefit Types: Covers Vacation (1), Sick Time (2), and Personal (3) benefits
Detailed Breakdown: Shows the components that make up the final balance
Active Employees Only: Focuses on currently active employees

How It Works

The report uses a complex query with multiple Common Table Expressions (CTEs) to calculate benefit balances:

LatestBalance CTE: Finds the most recent benefit balance record for each employee and benefit type
ManualEdits CTE: Calculates manual adjustments made to benefits (edit types 301 and 302)
BenefitUsage CTE: Tracks actual benefit usage from the benefit history table

The final calculation combines these components: Final Balance = Starting Balance + Manual Edits + Benefit Usage

Report Parameters

@AsOfDate: The date for which to calculate benefit balances (currently set to {mindate})

Report Output

The report provides the following information for each employee and benefit:

Employee Name: Last name, First name format
Benefit Name: Description of the benefit type
Starting Balance Date: Date of the most recent balance record
Starting Balance: Initial balance amount
Manual Edits: Net manual adjustments made
Benefit Usage: Total benefit hours used (negative values)
Balance As Of Date: Final calculated balance

Download Alternative Formats

📥 AOD Browser Report Format:

Download AOD Import File - Import this report directly into AOD Service Module.

SQL Code

benefit_balance_as_of_date.sql

DECLARE @AsOfDate DATE = {mindate};
-- <-- Enter your date here

-- MAIN QUERY - Employee Benefit Balance as of Date
;WITH
    LatestBalance
    AS
    (
        SELECT
            eb.FILEKEY,
            eb.BENEFIT,
            eb.PAYPERBEGINNING,
            eb.AMOUNT AS StartingBalance,
            ROW_NUMBER() OVER (
                PARTITION BY eb.FILEKEY, eb.BENEFIT
                ORDER BY eb.PAYPERBEGINNING DESC
            ) AS rn
        FROM dbo.EMPLOYEEBENEFITS eb
        WHERE eb.PAYPERBEGINNING <= @AsOfDate
    )
,
    ManualEdits
    AS
    (
        SELECT
            sh.FILEKEY,
            sh.PREVPAYDES AS BENEFIT,
            SUM(CASE WHEN sh.EDITTYPE = 301 THEN ISNULL(sh.HOURS,0) / 60.0
                     WHEN sh.EDITTYPE = 302 THEN -ISNULL(sh.HOURS,0) / 60.0
                     ELSE 0 END) AS NetChange
        FROM dbo.SUPEDITHIST sh
            INNER JOIN LatestBalance lb
            ON sh.FILEKEY = lb.FILEKEY
                AND sh.PREVPAYDES = lb.BENEFIT
        WHERE sh.EFFDATE > lb.PAYPERBEGINNING
            AND sh.EFFDATE <= @AsOfDate
            AND sh.EDITTYPE IN (301, 302)
            AND lb.rn = 1
        GROUP BY sh.FILEKEY, sh.PREVPAYDES
    )
,
    BenefitUsage
    AS
    (
        SELECT
            bh.FILEKEY,
            bh.BENEFIT,
            SUM(-ISNULL(bh.HOURS, 0)) AS NetChange
        FROM dbo.BENHIST bh
            INNER JOIN LatestBalance lb
            ON bh.FILEKEY = lb.FILEKEY
                AND bh.BENEFIT = lb.BENEFIT
        WHERE bh.EVENTDATE > lb.PAYPERBEGINNING
            AND bh.EVENTDATE <= @AsOfDate
            AND lb.rn = 1
        GROUP BY bh.FILEKEY, bh.BENEFIT
    )

SELECT
    lb.FILEKEY,
    e.LASTNAME + ', ' + e.FIRSTNAME AS EmployeeName,
    b.NAME AS BenefitName,
    lb.BENEFIT,
    lb.PAYPERBEGINNING AS StartingBalanceDate,
    ROUND(lb.StartingBalance, 2) AS StartingBalance,
    ROUND(ISNULL(me.NetChange, 0), 2) AS ManualEdits,
    ROUND(ISNULL(bu.NetChange, 0), 2) AS BenefitUsage,
    ROUND(lb.StartingBalance + ISNULL(me.NetChange, 0) + ISNULL(bu.NetChange, 0), 2) AS BalanceAsOfDate
FROM LatestBalance lb
    LEFT JOIN ManualEdits me ON lb.FILEKEY = me.FILEKEY AND lb.BENEFIT = me.BENEFIT
    LEFT JOIN BenefitUsage bu ON lb.FILEKEY = bu.FILEKEY AND lb.BENEFIT = bu.BENEFIT
    INNER JOIN dbo.BENEFITS b ON lb.BENEFIT = b.NUM
    INNER JOIN dbo.EMPLOYEES e ON lb.FILEKEY = e.FILEKEY
WHERE lb.rn = 1
    AND lb.BENEFIT IN (1,2,3)
    AND e.activestatus = 0
ORDER BY e.LASTNAME, e.FIRSTNAME, lb.BENEFIT

Content Inventory

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

Benefit Balance As Of Date

On this page