Aspen Tech Export With Points

Documentation for the Aspen Tech export script that generates an export file for Aspen Tech payroll system with incident points data

Parameters

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

Data Components

The report consists of two main sections combined with a UNION:

Hours-Based Payroll Data:
- Employee ID and name information (FILEKEY, IDNUM, full name)
- Work group codes (from WORKGROUP2)
- Hours for pay designations 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 18, 20
- Pay rates
- Date range information
- Current employee incident points (from past year)
Dollar-Based Payroll Data:
- Employee ID and name information (FILEKEY, IDNUM, full name)
- Work group codes (from WORKGROUP2)
- Dollar amounts for pay designation 100
- Pay rates
- Date range information
- Current employee incident points (from past year)

Technical Implementation

The script uses:

Two separate SELECT statements combined with UNION
Subquery to find the most recent incident point value for each employee
ISNULL function to handle potential NULL values in sums and incident points
Consistent date format using CAST for date parameters
Filtering by pay designation numbers to separate hour and dollar types
Comments to distinguish between the two sections ("hours type" and "dollar type")

T-SQL

aspen_tech_export_with_points.sql

--Pulls hours type paydes amounts
SELECT 
    E.FILEKEY,
    E.IDNUM,
    E.LASTNAME + ', ' + E.FIRSTNAME + ' ' + E.INITIAL,
    W2PD.CODE AS 'W2PDCODE',
    ISNULL(SUM(PD.HOURS), 0) AS 'HOURS',
    PD.PAYDESNUM,
    PD.RATE,
    CAST({mindate} AS DATETIME),
    CAST({maxdate} AS DATETIME),
    ISNULL(
        (SELECT TOP 1 RUNNINGPOINTS 
         FROM INCIDENTS 
         WHERE (EVENTDATE BETWEEN DATEADD(YEAR, -1, GETDATE()) AND GETDATE())
         AND FILEKEY = E.FILEKEY 
         ORDER BY EVENTDATE DESC),
        0.0
    )
FROM 
    EMPLOYEES E
    INNER JOIN PAYDESHIST PD ON E.FILEKEY = PD.FILEKEY
    LEFT JOIN WORKGROUP2 W2PD ON PD.WG2 = W2PD.WGNUM
WHERE
    PD.EVENTDATE >= {mindate}
    AND PD.EVENTDATE <= {maxdate}
    AND PD.PAYDESNUM IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 18, 20)
GROUP BY 
    E.FILEKEY,
    E.IDNUM,
    E.LASTNAME + ', ' + E.FIRSTNAME + ' ' + E.INITIAL,
    W2PD.CODE,
    PD.PAYDESNUM,
    PD.RATE

UNION

--Pulls dollar type paydes amounts
SELECT 
    E.FILEKEY,
    E.IDNUM,
    E.LASTNAME + ', ' + E.FIRSTNAME + ' ' + E.INITIAL,
    W2PD.CODE AS 'W2PDCODE',
    ISNULL(SUM(PD.DOLLARS), 0) AS 'DOLLARS',
    PD.PAYDESNUM,
    PD.RATE,
    CAST({mindate} AS DATETIME),
    CAST({maxdate} AS DATETIME),
    ISNULL(
        (SELECT TOP 1 RUNNINGPOINTS 
         FROM INCIDENTS 
         WHERE (EVENTDATE BETWEEN DATEADD(YEAR, -1, GETDATE()) AND GETDATE())
         AND FILEKEY = E.FILEKEY 
         ORDER BY EVENTDATE DESC),
        0.0
    )
FROM 
    EMPLOYEES E
    INNER JOIN PAYDESHIST PD ON E.FILEKEY = PD.FILEKEY
    LEFT JOIN WORKGROUP2 W2PD ON PD.WG2 = W2PD.WGNUM
WHERE
    PD.EVENTDATE >= {mindate}
    AND PD.EVENTDATE <= {maxdate}
    AND PD.PAYDESNUM IN (100)
GROUP BY 
    E.FILEKEY,
    E.IDNUM,
    E.LASTNAME + ', ' + E.FIRSTNAME + ' ' + E.INITIAL,
    W2PD.CODE,
    PD.PAYDESNUM,
    PD.RATE

Content Inventory

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