Madison Original LOW Export

Labor and Other Wages export for Madison with department-specific pay designation mapping

Parameters

{mindate} - Start date for the export period
{maxdate} - End date for the export period

Data Components

The report extracts and organizes the following data:

Employee ID - Modified IDNUM with location suffixes (-01, -02, -03)
Event Date - Date of the pay designation event
Pay Designation - Pay designation number
Pay Description - Pay designation name with department-specific suffixes
Hours/Dollars - Aggregated hours or dollar amounts
Workgroup 2 - Department/workgroup name
Custom Field - HR Custom Field 5
Static Field - Fixed value 'Hourly'

Technical Implementation

The script uses:

Location-Based ID Mapping - Appends location suffixes based on IDNUM patterns:
- IDs starting with '00715' or '03547' get '-02' suffix
- IDs starting with '02503' get '-03' suffix
- All others get '-01' suffix
Department-Specific Pay Designation Mapping - For shift premiums (PAYDESNUM 17 and 18), appends department codes:
- PROB (Probation), MNT (Maintenance), HWY (Highway), JYC (Juvenile Center)
- PER (Personnel), AUD (Auditor), JPS (Justice/Police Services)
- MCPD (Multiple County Police Departments)
Beeper Pay Processing - Separate UNION query for PAYDESNUM 19 and 21 (beeper pay)
Pay Class Filtering - Excludes pay class 7
Pay Designation Exclusions - Excludes PAYDESNUM 0, 9, 15, 16, 19, 20, 21, 22 from main query
Date Range Filtering - Uses {mindate} and {maxdate} parameters
Aggregation - Sums hours or dollars by employee, date, and pay designation

T-SQL

madison_Original_LOW.sql

select
    E.FILEKEY,
    CASE
        WHEN E.IDNUM LIKE '00715%' THEN E.IDNUM + '-02'
        WHEN E.IDNUM LIKE '03547%' THEN E.IDNUM + '-02'
        WHEN E.IDNUM LIKE '02503%' THEN E.IDNUM + '-03'
        ELSE E.IDNUM + '-01'
    END,
    PD.EVENTDATE,
    PD.PAYDESNUM,
    CASE
        PD.PAYDESNUM
        WHEN 17 THEN --.50 Shift Prem
        CASE
            PD.WG2
            WHEN 50 THEN PDS.NAME + '-PROB'
            WHEN 2 THEN PDS.NAME + '-MNT'
            WHEN 18 THEN PDS.NAME + '-HWY'
            WHEN 56 THEN PDS.NAME + '-JYC'
            WHEN 96 THEN PDS.NAME + '-PER'
            WHEN 69 THEN PDS.NAME + '-AUD'
            WHEN 48 THEN PDS.NAME + '-JPS'
            WHEN 70 THEN PDS.NAME + '-JYC'
            WHEN 104 THEN PDS.NAME + '-JPS'
            WHEN 22 THEN PDS.NAME + '-JYC'
            WHEN 26 THEN PDS.NAME + '-JYC'
            WHEN 100 THEN PDS.NAME + '-MCPD'
            WHEN 74 THEN PDS.NAME + '-MCPD'
            WHEN 29 THEN PDS.NAME + '-MCPD'
            WHEN 64 THEN PDS.NAME + '-MCPD'
            WHEN 36 THEN PDS.NAME + '-MCPD'
            WHEN 44 THEN PDS.NAME + '-MCPD'
            WHEN 76 THEN PDS.NAME + '-MCPD'
            WHEN 30 THEN PDS.NAME + '-MCPD'
            WHEN 8 THEN PDS.NAME + '-MCPD'
            WHEN 7 THEN PDS.NAME + '-MCPD'
            WHEN 43 THEN PDS.NAME + '-MCPD'
            WHEN 93 THEN PDS.NAME + '-MCPD'
            WHEN 85 THEN PDS.NAME + '-MCPD'
            WHEN 89 THEN PDS.NAME + '-MCPD'
            ELSE PDS.NAME + '-NOT'
        END
        WHEN 18 THEN --.75 Shift Prem
        CASE
            PD.WG2
            WHEN 50 THEN PDS.NAME + '-PROB'
            WHEN 2 THEN PDS.NAME + '-MNT'
            WHEN 18 THEN PDS.NAME + '-HWY'
            WHEN 56 THEN PDS.NAME + '-JYC'
            WHEN 96 THEN PDS.NAME + '-PER'
            WHEN 69 THEN PDS.NAME + '-AUD'
            WHEN 48 THEN PDS.NAME + '-JPS'
            WHEN 70 THEN PDS.NAME + '-JYC'
            WHEN 104 THEN PDS.NAME + '-JPS'
            WHEN 22 THEN PDS.NAME + '-JYC'
            WHEN 26 THEN PDS.NAME + '-JYC'
            WHEN 100 THEN PDS.NAME + '-MCPD'
            WHEN 74 THEN PDS.NAME + '-MCPD'
            WHEN 29 THEN PDS.NAME + '-MCPD'
            WHEN 64 THEN PDS.NAME + '-MCPD'
            WHEN 36 THEN PDS.NAME + '-MCPD'
            WHEN 44 THEN PDS.NAME + '-MCPD'
            WHEN 76 THEN PDS.NAME + '-MCPD'
            WHEN 30 THEN PDS.NAME + '-MCPD'
            WHEN 8 THEN PDS.NAME + '-MCPD'
            WHEN 7 THEN PDS.NAME + '-MCPD'
            WHEN 43 THEN PDS.NAME + '-MCPD'
            WHEN 93 THEN PDS.NAME + '-MCPD'
            WHEN 85 THEN PDS.NAME + '-MCPD'
            WHEN 89 THEN PDS.NAME + '-MCPD'
            ELSE PDS.NAME + '-NOT'
        END
        ELSE PDS.NAME
    END AS Name,
    isnull(sum(PD.HOURS), 0) as 'HOURS',
    W2PD.NAME as 'W2PDNAME',
    'Hourly' as STATIC7,
    HR.CUSTOM5
from EMPLOYEES E
    INNER JOIN PAYDESHIST PD on E.FILEKEY = PD.FILEKEY
    INNER JOIN PAYDESIGNATIONS PDS on PD.PAYDESNUM = PDS.PAYDESNUM
    LEFT JOIN WORKGROUP2 W2PD on PD.WG2 = W2PD.WGNUM
    INNER JOIN EMPLOYEEHR HR on E.FILEKEY = HR.OWNERID
where
    PD.EVENTDATE >= {mindate}
    and PD.EVENTDATE <= {maxdate}
    and PD.PAYDESNUM not in (0, 9, 15, 16, 19, 20, 21, 22)
    and E.payclass <> 7
group by
    E.FILEKEY,
    E.IDNUM,
    PD.EVENTDATE,
    PD.PAYDESNUM,
    PDS.NAME,
    W2PD.NAME,
    HR.CUSTOM5,
    E.FILEKEY,
    E.IDNUM,
    PD.EVENTDATE,
    PD.PAYDESNUM,
    PDS.NAME,
    W2PD.NAME,
    HR.CUSTOM5,
    PD.WG2

UNION

--For Beeper Pay
select
    E.FILEKEY,
    CASE
        WHEN E.IDNUM LIKE '00715%' THEN E.IDNUM + '-02'
        WHEN E.IDNUM LIKE '03547%' THEN E.IDNUM + '-02'
        WHEN E.IDNUM LIKE '02503%' THEN E.IDNUM + '-03'
        ELSE E.IDNUM + '-01'
    END,
    PD.EVENTDATE,
    PD.PAYDESNUM,
    CASE
        PD.PAYDESNUM
        WHEN 19 THEN CASE
            PD.WG2
            WHEN 50 THEN PDS.NAME + '-PROB'
            WHEN 2 THEN PDS.NAME + '-MNT'
            WHEN 18 THEN PDS.NAME + '-HWY'
            WHEN 56 THEN PDS.NAME + '-JYC'
            WHEN 22 THEN PDS.NAME + '-JYC'
            WHEN 69 THEN PDS.NAME + '-AUD'
            WHEN 48 THEN PDS.NAME + '-JPS'
            WHEN 104 THEN PDS.NAME + '-JPS'
            WHEN 13 THEN PDS.NAME + '-CJC'
            WHEN 98 THEN PDS.NAME + '-CJC'
            WHEN 31 THEN PDS.NAME + '-CJC'
            WHEN 54 THEN PDS.NAME + '-PRO'
            WHEN 46 THEN PDS.NAME + '-SUPPR'
            WHEN 16 THEN PDS.NAME + 'CORR'
            ELSE PDS.NAME + '-NOT'
        END
        ELSE PDS.NAME
    END AS Name,
    isnull(sum(PD.DOLLARS), 0) as 'DOLLARS',
    W2PD.NAME as 'W2PDNAME',
    'Hourly' as STATIC16,
    HR.CUSTOM5
from EMPLOYEES E
    INNER JOIN PAYDESHIST PD on E.FILEKEY = PD.FILEKEY
    INNER JOIN PAYDESIGNATIONS PDS on PD.PAYDESNUM = PDS.PAYDESNUM
    LEFT JOIN WORKGROUP2 W2PD on PD.WG2 = W2PD.WGNUM
    INNER JOIN EMPLOYEEHR HR on E.FILEKEY = HR.OWNERID
where
    PD.EVENTDATE>= {mindate}
    and PD.EVENTDATE <= {maxdate}
    and PD.PAYDESNUM IN (19, 21)
    and E.PAYCLASS <> 7
group by
    E.FILEKEY,
    E.IDNUM,
    PD.EVENTDATE,
    PD.PAYDESNUM,
    PDS.NAME,
    W2PD.NAME,
    HR.CUSTOM5,
    E.FILEKEY,
    E.IDNUM,
    PD.EVENTDATE,
    PD.PAYDESNUM,
    PDS.NAME,
    W2PD.NAME,
    HR.CUSTOM5,
    PD.WG2
order by 2,4

Content Inventory

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

Madison Original LOW Export

Parameters

Data Components

Technical Implementation

T-SQL

Content Inventory

On this page