LOW Payroll Export (Version 3)

Documentation for the LOW Payroll Export (Version 3) script that generates an export file for LOW payroll system

Download Alternative Formats

📥 Browser Export Format:

Download Export File - Download this export for external processing

AOD Parameters

{mindate} - Start date for the export period (template variable, example: '2025-03-01')
{maxdate} - End date for the export period (template variable, example: '2025-03-31')
{userid} - User ID for access control filtering (template variable)

Filtering Options

@HideRatesSalPayclass - Comma-delimited list of pay classes for which rates should be hidden (default: '*')
@PayClassFilter - Filter by included pay class (i.e.: '2,3,8,9')
@TimeLocFilter - Filter by included time location (i.e.: '1,2,29,36')
@ExportingPDs - List Pay designations to include in export (i.e.: '1,2,3,4,5,7,8,9,10,13,18,19,20,21,23')
@PDDecriptions - List Pay designations to show descriptions for (i.e.: '4,5,9,10,20,21')

Export Mode Controls (NEW in V3)

@MyEmployees - Toggle to apply user access filtering (0=off, 1=on)
@OnlyEvents - Toggle to show only events and overtime/doubletime (0=off, 1=on)
@OnlyEventsAndUnpaid - Toggle to show events, overtime/doubletime, and unpaid hours (0=off, 1=on)
@SpecialRateDist - Comma-delimited list of WG7 values for special rate distribution handling (default: '*')

Pay Type Categorization

@OT - Overtime pay designations
@SOT - Straight overtime pay designations
@DBL - Double time pay designations
@ABS - Absence pay designations
@CompE - Comp time earned pay designations
@CompP - Comp time paid pay designations
@Unpaid - Unpaid hours pay designations
@HEvent - Hard Coded event pay designations

Data Components

The report dynamically categorizes hours into attendance types based on configurable parameters:

Regular Hours - Default category
Event Hours - When WG7 name starts with '*' or based on pay designations in @HEvent
Overtime Hours - Based on pay designations in @OT parameter
STR OT (Straight Overtime) - Based on pay designations in @SOT parameter
DoubleTime - Based on pay designations in @DBL parameter
Absence - Based on pay designations in @ABS parameter
CompTime (Earned) - Based on pay designations in @CompE parameter
CompTime Paid - Based on pay designations in @CompP parameter
Unpaid - Based on pay designations in @Unpaid parameter

Output Format

The export generates the following columns:

Pay Record ID - Employee ID with 'B' suffix removed plus workgroup 6 value
Attendance Type - Dynamically determined based on parameters and logic
Attendance Date - Event date from pay designation history
Absence - NULL (reserved for future use)
Days/Hours - Hours worked/taken from pay designation
Work Location - From WG5 (Workgroup 5)
Work Pay Group - NULL (reserved for future use)
Comment - NULL (reserved for future use)
Description - WG7 name (without '*') or pay designation name based on configuration
Special Rate/Dist - WG7 name when special rate distribution applies
Special Rate - Pay rate (conditionally hidden based on pay class)
Special Distribution - NULL (reserved for future use)

Export Mode Controls

Version 3 introduces sophisticated filtering modes:

Standard Mode (All switches OFF)

Exports all records matching other criteria
No special filtering based on event or unpaid status

Events Only Mode (`@OnlyEvents = 1`)

Includes only:
- Records where WG7.name starts with '*' (event indicators)
- Records with pay designations in @OT, @SOT, or @DBL categories

Events and Unpaid Mode (`@OnlyEventsAndUnpaid = 1`)

Includes only:
- Records where WG7.name starts with '*' (event indicators)
- Records with pay designations in @OT, @SOT, @DBL, or @Unpaid categories

Special Rate Distribution Mode (`@SpecialRateDist = 'WG7_numbers'`)

When set to specific WG7 (Workgroup 7) numbers (e.g., '5,10,15'), records matching these WG7 values will:
- Be categorized as 'Regular' attendance type (overriding other categorization rules)
- Have their Description field set to NULL
- Have their Special Rate/Dist field populated with the WG7 name (without any '*' prefix)
Use case: Special work assignments or projects that need custom rate distributions while maintaining regular attendance classification
When set to '*' (default): This special handling is disabled and normal categorization rules apply

Employee Access Control

The @MyEmployees parameter provides user-based access control:

When enabled (1): Results are filtered to only include employees the user has access to based on the USERACCESS table
When disabled (0): All employees meeting other criteria are included regardless of user permissions
Access Logic: Checks user permissions across all workgroup levels (WG1-WG7), where users have access if:
- They have specific access to that workgroup, OR
- They have universal access (when workgroup value = 0)

Performance Optimizations (NEW in V3)

Version 3 includes significant performance improvements:

Pre-calculated STRING_SPLIT Operations

Uses a Common Table Expression (CTE) called SplitParams to perform all STRING_SPLIT operations once
Eliminates repetitive string parsing in WHERE clauses and CASE expressions
Improves query execution time, especially with large datasets

Optimized Parameter Handling

Better handling of wildcard values ('*') in parameter checks
More efficient filtering logic with reduced subquery complexity
Improved NULL handling with TRY_CAST operations

Improvements Over V2

Performance Optimization: Pre-calculated STRING_SPLIT operations via CTE significantly improve query performance
Enhanced Export Modes: New @OnlyEventsAndUnpaid toggle provides additional filtering granularity
Improved Parameter Handling: Better wildcard ('*') handling and NULL safety
Optimized Filtering Logic: Reduced subquery complexity in WHERE clauses
Better Maintainability: Centralized STRING_SPLIT operations make the code easier to maintain

T-SQL

low_payroll_export_v3.sql

DECLARE @MIN DATE, @MAX DATE
SET @MIN={mindate}
SET @MAX={maxdate}

--------Parameters Section----------
DECLARE @HideRatesSalPayclass NVARCHAR(MAX) = '2,3,4,5,6,7,8,20'
DECLARE @PayClassFilter NVARCHAR(MAX) = '9,10,11,12,13,14,15,16,17,18,19,20'
DECLARE @TimeLocFilter NVARCHAR(MAX) = '*'
DECLARE @OnlyEvents BIT = 0 -- 0 = OFF, 1 = ON
DECLARE @OnlyEventsAndUnpaid BIT = 0 -- 0 = OFF, 1 = ON (shows events + unpaid hours)
DECLARE @MyEmployees BIT = 0 -- 0 = OFF, 1 = ON
DECLARE @SpecialRateDist NVARCHAR(MAX) = '*'
DECLARE @ExportingPDs NVARCHAR(MAX) = '1,2,3,4,5,6,7,8,9,10,12,13,16,18,19,20,21,22,23'
DECLARE @PDDecriptions NVARCHAR(MAX) = '3,4,5,7,8,9,10,13,16,18,19,20,21,22,23'
DECLARE @OT NVARCHAR(MAX) = '2,19'
DECLARE @SOT NVARCHAR(MAX) = '*'
DECLARE @DBL NVARCHAR(MAX) = '*'
DECLARE @ABS NVARCHAR(MAX) = '3,4,5,7,8,9,10,20,21,22,27,28,29,30'
DECLARE @CompE NVARCHAR(MAX) = '*'
DECLARE @CompP NVARCHAR(MAX) = '12'
DECLARE @Unpaid NVARCHAR(MAX) = '6,13,16'
DECLARE @HEvent NVARCHAR(MAX) = '23'

-- Pre-calculate STRING_SPLIT operations for performance
;WITH
    SplitParams
    AS
    (
        SELECT 'ExportingPDs' AS ParamType, TRY_CAST(value AS INT) AS IntValue
            FROM STRING_SPLIT(@ExportingPDs, ',')
            WHERE TRY_CAST(value AS INT) IS NOT NULL
        UNION ALL
            SELECT 'PDDecriptions', TRY_CAST(value AS INT)
            FROM STRING_SPLIT(@PDDecriptions, ',')
            WHERE TRY_CAST(value AS INT) IS NOT NULL
        UNION ALL
            SELECT 'SpecialRateDist', TRY_CAST(value AS INT)
            FROM STRING_SPLIT(@SpecialRateDist, ',')
            WHERE TRY_CAST(value AS INT) IS NOT NULL AND @SpecialRateDist != '*'
        UNION ALL
            SELECT 'OT', TRY_CAST(value AS INT)
            FROM STRING_SPLIT(@OT, ',')
            WHERE TRY_CAST(value AS INT) IS NOT NULL AND @OT != '*'
        UNION ALL
            SELECT 'SOT', TRY_CAST(value AS INT)
            FROM STRING_SPLIT(@SOT, ',')
            WHERE TRY_CAST(value AS INT) IS NOT NULL AND @SOT != '*'
        UNION ALL
            SELECT 'DBL', TRY_CAST(value AS INT)
            FROM STRING_SPLIT(@DBL, ',')
            WHERE TRY_CAST(value AS INT) IS NOT NULL AND @DBL != '*'
        UNION ALL
            SELECT 'ABS', TRY_CAST(value AS INT)
            FROM STRING_SPLIT(@ABS, ',')
            WHERE TRY_CAST(value AS INT) IS NOT NULL AND @ABS != '*'
        UNION ALL
            SELECT 'CompE', TRY_CAST(value AS INT)
            FROM STRING_SPLIT(@CompE, ',')
            WHERE TRY_CAST(value AS INT) IS NOT NULL AND @CompE != '*'
        UNION ALL
            SELECT 'CompP', TRY_CAST(value AS INT)
            FROM STRING_SPLIT(@CompP, ',')
            WHERE TRY_CAST(value AS INT) IS NOT NULL AND @CompP != '*'
        UNION ALL
            SELECT 'Unpaid', TRY_CAST(value AS INT)
            FROM STRING_SPLIT(@Unpaid, ',')
            WHERE TRY_CAST(value AS INT) IS NOT NULL AND @Unpaid != '*'
        UNION ALL
            SELECT 'HEvent', TRY_CAST(value AS INT)
            FROM STRING_SPLIT(@HEvent, ',')
            WHERE TRY_CAST(value AS INT) IS NOT NULL AND @HEvent != '*'
        UNION ALL
            SELECT 'TimeLocFilter', TRY_CAST(value AS INT)
            FROM STRING_SPLIT(@TimeLocFilter, ',')
            WHERE TRY_CAST(value AS INT) IS NOT NULL AND @TimeLocFilter != '*'
        UNION ALL
            SELECT 'PayClassFilter', TRY_CAST(value AS INT)
            FROM STRING_SPLIT(@PayClassFilter, ',')
            WHERE TRY_CAST(value AS INT) IS NOT NULL AND @PayClassFilter != '*'
        UNION ALL
            SELECT 'HideRatesSalPayclass', TRY_CAST(value AS INT)
            FROM STRING_SPLIT(@HideRatesSalPayclass, ',')
            WHERE TRY_CAST(value AS INT) IS NOT NULL AND @HideRatesSalPayclass != '*'
    )
SELECT
    CONCAT(
        CASE WHEN RIGHT(E.IDNUM,1)='B' THEN LEFT(E.IDNUM,LEN(E.IDNUM)-1) ELSE E.IDNUM END,
        '-0',
        CONVERT(VARCHAR, pd.WG6)
    ) AS 'Pay Record ID',
    CASE
        WHEN @SpecialRateDist != '*' AND PD.WG7 IN (SELECT IntValue
        FROM SplitParams
        WHERE ParamType = 'SpecialRateDist') THEN 'Regular'
        WHEN WG7.name LIKE '[*]%' THEN 'Event'
        WHEN @OT != '*' AND pd.PAYDESNUM IN (SELECT IntValue
        FROM SplitParams
        WHERE ParamType = 'OT') THEN 'Overtime'
        WHEN @SOT != '*' AND pd.PAYDESNUM IN (SELECT IntValue
        FROM SplitParams
        WHERE ParamType = 'SOT') THEN 'STR OT'
        WHEN @DBL != '*' AND pd.PAYDESNUM IN (SELECT IntValue
        FROM SplitParams
        WHERE ParamType = 'DBL') THEN 'DoubleTime'
        WHEN @ABS != '*' AND pd.PAYDESNUM IN (SELECT IntValue
        FROM SplitParams
        WHERE ParamType = 'ABS') THEN 'Absence'
        WHEN @CompE != '*' AND pd.PAYDESNUM IN (SELECT IntValue
        FROM SplitParams
        WHERE ParamType = 'CompE') THEN 'CompTime (Earned)'
        WHEN @CompP != '*' AND pd.PAYDESNUM IN (SELECT IntValue
        FROM SplitParams
        WHERE ParamType = 'CompP') THEN 'CompTime Paid'
        WHEN @Unpaid != '*' AND pd.PAYDESNUM IN (SELECT IntValue
        FROM SplitParams
        WHERE ParamType = 'Unpaid') THEN 'Unpaid'
        WHEN @HEvent != '*' AND pd.PAYDESNUM IN (SELECT IntValue
        FROM SplitParams
        WHERE ParamType = 'HEvent') THEN 'Event'
        ELSE 'Regular'
    END AS 'Attendance Type',
    pd.EVENTDATE AS 'Attendance Date',
    CASE 
        WHEN @ABS != '*' AND pd.PAYDESNUM IN (SELECT IntValue
        FROM SplitParams
        WHERE ParamType = 'ABS') THEN PDS.NAME
        ELSE NULL
    END AS Absence,
    pd.HOURS AS 'Days/Hours',
    WG5.NAME AS 'Work Location',
    NULL AS 'Work Pay Group',
    NULL AS 'Comment',
    CASE 
        WHEN @SpecialRateDist != '*' AND PD.WG7 IN (SELECT IntValue
        FROM SplitParams
        WHERE ParamType = 'SpecialRateDist') THEN NULL
        WHEN WG7.name LIKE '[*]%' THEN RIGHT(WG7.name, LEN(WG7.Name) - 1) 
        WHEN pd.PAYDESNUM IN (SELECT IntValue
        FROM SplitParams
        WHERE ParamType = 'PDDecriptions')
        AND NOT (pd.PAYDESNUM IN (SELECT IntValue
        FROM SplitParams
        WHERE ParamType = 'ABS')) THEN PDS.NAME
        ELSE NULL
    END AS 'Description',
    CASE 
        WHEN @SpecialRateDist != '*' AND PD.WG7 IN (SELECT IntValue
        FROM SplitParams
        WHERE ParamType = 'SpecialRateDist') THEN RIGHT(WG7.name, LEN(WG7.Name) - 1)
        ELSE NULL
    END AS 'Special Rate/Dist',
    CASE
        WHEN @HideRatesSalPayclass != '*' AND e.PAYCLASS IN (SELECT IntValue
        FROM SplitParams
        WHERE ParamType = 'HideRatesSalPayclass') THEN NULL
        ELSE pd.RATE 
    END AS 'Special Rate',
    NULL AS 'Special Distribution'
FROM PAYDESHIST PD
    INNER JOIN EMPLOYEES E ON E.FILEKEY = PD.FILEKEY
    INNER JOIN WORKGROUP5 WG5 ON PD.WG5 = WG5.WGNUM
    INNER JOIN WORKGROUP7 WG7 ON PD.WG7 = WG7.WGNUM
    INNER JOIN PAYDESIGNATIONS PDS ON PD.PAYDESNUM = PDS.PAYDESNUM
WHERE pd.EVENTDATE BETWEEN @MIN AND @MAX
    AND pd.PAYDESNUM IN (SELECT IntValue
    FROM SplitParams
    WHERE ParamType = 'ExportingPDs')
    AND (
        (@OnlyEvents = 0 AND @OnlyEventsAndUnpaid = 0) -- If both switches are OFF, include all records
    OR
    (
        @OnlyEvents = 1 AND @OnlyEventsAndUnpaid = 0 AND -- Only Events switch
    (
            WG7.name LIKE '[*]%' -- Always include WG7.name starting with *
    OR
    pd.PAYDESNUM IN (  -- Include PDs listed in @OT, @SOT, or @DBL
                    SELECT TRY_CAST(value AS INT)
    FROM (
                                SELECT value
            FROM STRING_SPLIT(@OT, ',')
            -- Split @OT
        UNION ALL
            SELECT value
            FROM STRING_SPLIT(@SOT, ',')
        -- Split @SOT
        UNION ALL
            SELECT value
            FROM STRING_SPLIT(@DBL, ',')  -- Split @DBL
                    ) AS CombinedPDs
    WHERE TRY_CAST(value AS INT) IS NOT NULL  -- Ignore non-integer values (e.g., '*')
                )
        )
    )
    OR
    (
        @OnlyEventsAndUnpaid = 1 AND -- Events and Unpaid switch
    (
            WG7.name LIKE '[*]%' -- Always include WG7.name starting with *
    OR
    pd.PAYDESNUM IN (  -- Include PDs listed in @OT, @SOT, @DBL, or @Unpaid
                    SELECT TRY_CAST(value AS INT)
    FROM (
                                        SELECT value
            FROM STRING_SPLIT(@OT, ',')
            -- Split @OT
        UNION ALL
            SELECT value
            FROM STRING_SPLIT(@SOT, ',')
        -- Split @SOT
        UNION ALL
            SELECT value
            FROM STRING_SPLIT(@DBL, ',')
        -- Split @DBL
        UNION ALL
            SELECT value
            FROM STRING_SPLIT(@Unpaid, ',')  -- Split @Unpaid
                    ) AS CombinedPDs
    WHERE TRY_CAST(value AS INT) IS NOT NULL  -- Ignore non-integer values (e.g., '*')
                )
        )
    )
    )
    AND e.idnum != '999999999'
    AND e.ACTIVESTATUS=0
    AND (
        @TimeLocFilter = '*'
    OR pd.WG5 IN (SELECT IntValue
    FROM SplitParams
    WHERE ParamType = 'TimeLocFilter')
    )
    AND (
        @PayClassFilter = '*'
    OR e.payclass IN (SELECT IntValue
    FROM SplitParams
    WHERE ParamType = 'PayClassFilter')
    )
    AND (
        @MyEmployees = 0 -- If @MyEmployees is OFF, ignore employee access check
    OR
    (
            (SELECT COUNT(*)
    FROM USERACCESS U
    WHERE (U.USERID={userid}) AND (((U.WG1=E.WG1) OR (U.WG1=0)) AND ((U.WG2=E.WG2) OR (U.WG2=0)) AND ((U.WG3=E.WG3) OR (U.WG3=0)) AND ((U.WG4=E.WG4) OR (U.WG4=0)) AND ((U.WG5=E.WG5) OR (U.WG5=0)) AND ((U.WG6=E.WG6) OR (U.WG6=0)) AND ((U.WG7=E.WG7) OR (U.WG7=0))))>0
        )
    )
ORDER BY 1,3;

Content Inventory

Doc File: content/docs/payroll_exports/low_payroll_export_v3.mdx
SQL Script: SQL/payroll_exports/low_payroll_export_v3.sql

LOW Payroll Export (Version 3)

On this page