Basic Pay Export

This report generates a comprehensive pay export in a standardized format suitable for external payroll systems or accounting software. It combines both hourly and dollar-based pay designations into a unified export format with employee and workgroup information.

Parameters

@MIN - Start date for the pay period (example: '2/15/2021')
@MAX - End date for the pay period (example: '2/28/2021')

Data Components

The report consists of 2 main data sections combined via UNION:

Hourly Pay Records - Time-based pay designations with hours worked
Dollar Pay Records - Fixed amount pay designations with dollar values

Output Format

The report returns pay data with the following structure:

Column	Description
filekey	Employee file key
IDNUM	Employee ID number
Record Type	Always 'E' for employee
Pay Code	Pay designation abbreviation
Hours/Amount	Hours worked or '0' for dollar records
Dollars	Dollar amount or hours for hourly records
Workgroup1 Code	Department code
Workgroup2 Code	Location/shift code
Event Date	Pay event date
Process Date	Same as event date

Technical Implementation

The script uses:

UNION operation to combine hourly and dollar-based records
Multiple table JOINs for employee and pay designation details
Workgroup code lookups for organizational structure
Comprehensive pay designation filtering
Date range filtering with BETWEEN operations

Pay Designation Categories

Hourly Pay Designations (with hours): 1,2,3,4,5,6,7,8,9,10,11,12,13,15,16,18,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,43,44,45,46,47,48

Dollar Pay Designations (with amounts): 38,39,40,41,42,49,50,51,52

Notes

Exports all pay designations within specified categories
Maintains separate handling for hourly vs. dollar-based pay
Includes workgroup codes for organizational reporting
Results sorted by employee ID and event date
Format designed for external system compatibility
Covers comprehensive range of pay types including regular, overtime, PTO, and special pays
Standard export format for payroll processing workflows

T-SQL

basic_pay.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE,
              @MAX DATE
/*              
SET @MIN={mindate}
SET @MAX={maxdate}
*/
Set @MIN='2/15/2021'
SET @MAX='2/28/2021'

Select
e.filekey,
e.IDNUM,
'E',
pds.ABB,
pd.HOURS,
'','','',
w1.code,
w2.code,
'','','','','',
pd.EVENTDATE,
pd.EVENTDATE

From Employees e, paydeshist pd, PAYDESIGNATIONS pds, WORKGROUP1 w1, WORKGROUP2 w2
Where e.filekey=pd.filekey
and pd.PAYDESNUM=pds.PAYDESNUM
and pd.WG1=w1.WGNUM
and pd.WG2=w2.WGNUM
and pd.eventdate between @min and @max
and pd.PAYDESNUM in (1,2,3,4,5,6,7,8,9,10,11,12,13,15,16,18,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,43,44,45,46,47,48)

UNION

Select
e.filekey,
e.IDNUM,
'E',
pds.ABB,
'0',
pd.DOLLARS,
'','',
w1.code,
w2.code,
'','','','','',
pd.EVENTDATE,
pd.EVENTDATE

From Employees e, paydeshist pd, PAYDESIGNATIONS pds, WORKGROUP1 w1, WORKGROUP2 w2
Where e.filekey=pd.filekey
and pd.PAYDESNUM=pds.PAYDESNUM
and pd.WG1=w1.WGNUM
and pd.WG2=w2.WGNUM
and pd.eventdate between @min and @max
and pd.PAYDESNUM in (38,39,40,41,42,49,50,51,52)

order by e.idnum,pd.eventdate

Content Inventory

Doc File: content/docs/reports/pay_information/basic_pay.mdx
SQL Script: SQL/reports/pay_information/basic_pay.sql

Basic Pay Export

On this page