Comp Greater Than

Hyper Query for filtering employees based on compensation benefit balances.

Overview

This query identifies employees whose compensation benefit (Benefit 7) balance meets or exceeds a specified threshold. It uses a window function to retrieve the most recent benefit record for each employee and filters based on the remaining balance.

Technical Implementation

The query uses:

• Window Function - ROW_NUMBER() partitioned by FILEKEY to get the most recent benefit record
• Benefit Filtering - Specifically targets Benefit 7 (compensation/comp time)
• Balance Threshold - Filters for employees with REMAIN >= 35
• Ordering - Orders by PAYPERBEGINNING DESC and UNIQUEID DESC to ensure the latest record

T-SQL

e.filekey in (
 
SELECT dt.FILEKEY
FROM (
    SELECT 
        FILEKEY,
        REMAIN,
        ROW_NUMBER() OVER (
            PARTITION BY FILEKEY 
            ORDER BY PAYPERBEGINNING DESC, UNIQUEID DESC
        ) AS Rn
    FROM EMPLOYEEBENEFITS
    WHERE BENEFIT = 7
) dt
WHERE dt.Rn = 1 
  AND dt.REMAIN >= 35
)

Content Inventory

• Doc File: content/docs/hyper-query/comp_greater_than.mdx
• SQL Script: SQL/hyper_query/comp_greater_than.sql