East Porter LOW Export

Documentation for the East Porter LOW export script that generates an export file for East Porter payroll system

Parameters

{mindate} - Start date for the export period (example: '6/18/2020')
{maxdate} - End date for the export period (example: '7/1/2020')
@SalPayclass - Table defining salary pay classes (configurable, default includes classes 2, 3, 4, 5, 6, 9, 12)

Data Components

The report organizes data into multiple sections based on attendance type:

Regular Hours:
- Regular time entries (pay designations 1, 10, 12, 13, 14, 18, 19, 21)
- Special event handling for events marked with an asterisk (*) in WG3
- Custom description mapping based on pay designation
Overtime Hours:
- Overtime entries (pay designations 2, 11, 20)
- Special event handling similar to regular hours
- Custom description mapping
Absence Hours:
- Various absence types (pay designations 3, 4, 5, 6, 7, 9, 15, 16, 22, 23)
- Mapped to specific absence codes (Vacation, Holiday, Personal, etc.)
- Includes work location and special rate information
Unpaid Hours:
- Unpaid time entries (pay designation 17)
- Follows same format as other categories

Output Format

The export generates columns in the following format:

Pay Record ID (Employee ID with work group suffix)
Attendance Type (Regular, Overtime, Absence, Unpaid, Event)
Event Date (formatted as MM/dd/yyyy)
Absence Type (when applicable)
Hours
Work Location
Additional fields for pay groups, comments, descriptions
Special Rate (except for salaried employees)

Technical Implementation

The script uses:

Temporary tables to organize data
Multiple INSERT statements for different attendance types
CASE statements for complex category mapping
String manipulation to handle event descriptions
Special handling for salaried employees (hiding rates)
Date formatting for consistent output

T-SQL

east_porter_low_export.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE,
		@MAX DATE
/*
SET @MIN={mindate}
SET @MAX={maxdate}
*/
SET @MIN='6/18/2020'
SET @MAX='7/1/2020'

-------------------Define your salary payclasses after "Values"
DECLARE	@SalPayclass Table(Num smallint)
insert into @SalPayclass(Num) Values (2),(3),(4),(5),(6),(9),(12)

DECLARE @LOW TABLE(PayRecordID VARCHAR(50), AttType VARCHAR(50), EventDate Date, Absence VARCHAR(50), HRS DECIMAL(38,2), WORKLOC VARCHAR(50), PAYGROUP VARCHAR(50),
	COMMENT VARCHAR(50), DESCRIPTION Varchar(25), RATEDIST Varchar(50), SpecialRate real, SpecialDIST varchar(50))

----------------------------REG HOURS
INSERT INTO @LOW 
	SELECT 
		E.IDNUM+'-0'+Convert(varchar,pd.wg6),
		CASE
			When WG3.name like'[*]%' then 'Event'
			ELSE 'Regular'
		End,
		pd.EVENTDATE,
		NULL,
		pd.HOURS,
		WG1.NAME,
		NULL,NULL,
		CASE
			When WG3.name like'[*]%' then RIGHT(WG3.name, LEN(WG3.Name) - 1)
			When pd.PAYDESNUM = 12 Then 'Other'
			When pd.PAYDESNUM = 13 Then 'SchBusine'
			When pd.PAYDESNUM = 14 Then 'Field Trip'
			When pd.PAYDESNUM = 18 Then 'Pro Dev'
			When pd.PAYDESNUM = 19 Then 'CntractReg'
			When pd.PAYDESNUM = 21 Then 'Service Hr'
			ELSE NULL
		End,
		NULL,
		Case
			When e.PAYCLASS IN (Select num from @SalPayclass) Then null
			ELSE pd.RATE
		END,
		null

FROM WORKGROUP1 WG1,  WORKGROUP3 WG3, EMPLOYEES E, PAYDESHIST PD 
WHERE e.FILEKEY=pd.FILEKEY 
	and pd.WG1=WG1.WGNUM
	and pd.WG3=wg3.WGNUM
	and pd.EVENTDATE between @MIN and @MAX
	and pd.PAYDESNUM in (1,10,12,13,14,18,19,21)
    and pd.hours > 0
	and e.idnum != '999999999' --Test employee
	and e.ACTIVESTATUS=0

----------------------------OT HOURS
INSERT INTO @LOW 
	SELECT 
		E.IDNUM+'-0'+Convert(varchar,pd.wg6),
		CASE
			When WG3.name like'[*]%' then 'Event'
			ELSE 'Overtime'
		End,
		pd.EVENTDATE,
		NULL,
		pd.HOURS,
		WG1.NAME,
		NULL,NULL,
		CASE
			When WG3.name like'[*]%' then RIGHT(WG3.name, LEN(WG3.Name) - 1)
			When PD.PAYDESNUM = 20 then 'CntractOT'
			ELSE NULL
		End,
		NULL,
		Case
			When e.PAYCLASS IN (Select num from @SalPayclass) Then null
			ELSE pd.RATE
		END,
		null

FROM WORKGROUP1 WG1,  WORKGROUP3 WG3, EMPLOYEES E, PAYDESHIST PD 
WHERE e.FILEKEY=pd.FILEKEY 
	and pd.WG1=WG1.WGNUM
	and pd.WG3=wg3.WGNUM
	and pd.EVENTDATE between @MIN and @MAX
	and pd.PAYDESNUM in (2,11,20)
	and e.idnum != '999999999' --Test employee
	and e.ACTIVESTATUS=0

/*----------------------------Hard Coded Events Hours
INSERT INTO @LOW SELECT E.IDNUM+'-0'+Convert(varchar,pd.wg6),
'Event',
pd.EVENTDATE,NULL,pd.HOURS,WG5.NAME,NULL,NULL,
CASE
    When pd.PAYDESNUM = 11 Then 'Court Pay'
    When pd.PAYDESNUM = 16 Then 'On-Call'
    When pd.PAYDESNUM = 26 Then 'WorkedHolday'
    Else null
End,
NULL,
Case
	When e.PAYCLASS IN (Select num from @SalPayclass) Then null
	ELSE pd.RATE
END,
null

FROM WORKGROUP5 WG5, WORKGROUP7 WG7, EMPLOYEES E, PAYDESHIST PD 
WHERE e.FILEKEY=pd.FILEKEY 
	and pd.WG5=WG5.WGNUM
	and pd.WG7=wg7.WGNUM
	and pd.EVENTDATE between @MIN and @MAX
	and pd.PAYDESNUM in (11,16,26)
	and e.idnum != '999999999' --Test employee
	and e.ACTIVESTATUS=0
*/

----------------------------Absence HOURS
INSERT INTO @LOW 
	SELECT 
		E.IDNUM+'-0'+Convert(varchar,pd.wg6),
		'Absence',
		pd.EVENTDATE,
		Case
			When PD.PAYDESNUM = 3 then 'Vacation'
			When PD.PAYDESNUM = 4 then 'Holiday'
			When PD.PAYDESNUM = 5 then 'Personal'
			When PD.PAYDESNUM = 6 then 'Jury Duty'
			When PD.PAYDESNUM = 7 then 'Sick'
			When PD.PAYDESNUM = 9 then 'Bereavment'
			When PD.PAYDESNUM = 15 then 'Float Hol'
			When PD.PAYDESNUM = 16 then 'Lost Day'
			When PD.PAYDESNUM = 22 then 'CorpPdDay'
			When PD.PAYDESNUM = 23 then 'COVID19'
		End,
		pd.HOURS,
		WG1.NAME,
		NULL,NULL,Null,NULL,
		Case
			When e.PAYCLASS IN (Select num from @SalPayclass) Then null
			ELSE pd.RATE
		END,
		null

FROM WORKGROUP1 WG1,  WORKGROUP3 WG3, EMPLOYEES E, PAYDESHIST PD 
WHERE e.FILEKEY=pd.FILEKEY 
	and pd.WG1=WG1.WGNUM
	and pd.WG3=wg3.WGNUM
	and pd.EVENTDATE between @MIN and @MAX
	and pd.PAYDESNUM in (3,4,5,6,7,9,15,16,22,23)
    and pd.hours > 0
	and e.idnum != '999999999' --Test employee
	and e.ACTIVESTATUS=0

----------------------------UNPAID Hours
INSERT INTO @LOW 
	SELECT 
		E.IDNUM+'-0'+Convert(varchar,pd.wg6),
		'Unpaid',
		pd.EVENTDATE,
		NULL,
		pd.HOURS,
		WG1.NAME,
		NULL,NULL,Null,NULL,
		Case
			When e.PAYCLASS IN (Select num from @SalPayclass) Then null
			ELSE pd.RATE
		END,
		null

FROM WORKGROUP1 WG1,  WORKGROUP3 WG3, EMPLOYEES E, PAYDESHIST PD 
WHERE e.FILEKEY=pd.FILEKEY 
	and pd.WG1=WG1.WGNUM
	and pd.WG3=wg3.WGNUM
	and pd.EVENTDATE between @MIN and @MAX
	and pd.PAYDESNUM in (17)
	and e.idnum != '999999999' --Test employee
	and e.ACTIVESTATUS=0

----------------------------Display Results
Select PayRecordID, AttType, format(EventDate,'MM/dd/yyyy'), Absence, Hrs,WORKLOC, PAYGROUP, COMMENT, DESCRIPTION, RATEDIST, SpecialRate, SpecialDIST from @LOW order by 1,3

Content Inventory

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